StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It's not pretty.</p> <ul> <li>Join LettingUnitBookings to itself</li> <li>Find the start and end of gaps between bookings for each <code>F_LU_ID</code></li> <li>Get the size of the gaps - the available 'slots'</li> <li>Consider the case where there are no existing bookings that 'bracket' a suitable slot, add in outlier dates for this</li> <li>Join that projection to the LettingUnits table and apply <code>WHERE</code> criteria (start, end, duration)</li> </ul> <p>I've neglected to include BookingUnits that have no bookings at all.</p> <p>Ends up looking like this:</p> <pre><code>SELECT @StartOfWindow := '2009-01-01', @EndOfWindow := '2009-02-01', @WindowSize := 5 ; SELECT lu.Name, Slots.* FROM ( SELECT lub1.F_LU_ID, DATE_ADD( MAX( lub2.date_time ), INTERVAL 1 DAY ) AS StartOfSlot, DATE_SUB( lub1.date_time, INTERVAL 1 DAY ) AS EndOfSlot, DATEDIFF( lub1.date_time, MAX( lub2.date_time ) ) - 1 AS AvailableDays FROM ( SELECT F_LU_ID, Start AS date_time FROM LettingUnitBookings UNION SELECT F_LU_ID, CAST( '9999-12-31' AS DATE ) FROM LettingUnitBookings ) AS lub1, ( SELECT F_LU_ID, End AS date_time FROM LettingUnitBookings UNION SELECT F_LU_ID, CAST( '1000-01-01' AS DATE ) FROM LettingUnitBookings ) AS lub2 WHERE lub2.date_time <= lub1.date_time AND lub2.F_LU_ID = lub1.F_LU_ID GROUP BY lub1.F_LU_ID, lub1.date_time ) Slots JOIN LettingUnits lu ON lu.ID = Slots.F_LU_ID WHERE Slots.AvailableDays >= @WindowSize AND ( ( DATEDIFF( Slots.EndOfSlot, @EndOfWindow ) >= @WindowSize AND DATEDIFF( @StartOfWindow, Slots.StartOfSlot ) >= @WindowSize ) OR ( DATEDIFF( @EndOfWindow, Slots.StartOfSlot ) >= @WindowSize AND DATEDIFF( Slots.EndOfSlot, @StartOfWindow ) >= @WindowSize ) ) </code></pre> <p>Gives</p> <pre><code>Name F_LU_ID StartOfSlot EndOfSlot AvailableDays Foo Cottage 123 2009-01-06 2009-01-09 5 Foo Cottage 123 2009-01-21 2009-01-24 5 </code></pre> <p>Hopefully that can be adapted to suit your needs.</p> <p>Alternatively, if a booking can start on the same day that the previous booking ends, you can adapt slightly...</p> <pre><code>SELECT lu.Name, Slots.* FROM ( SELECT lub1.F_LU_ID, MAX( lub2.date_time ) AS StartOfSlot, lub1.date_time AS EndOfSlot, DATEDIFF( lub1.date_time, MAX( lub2.date_time )) AS AvailableDays FROM ( SELECT F_LU_ID, Start AS date_time FROM LettingUnitBookings UNION SELECT F_LU_ID, CAST( '9999-12-31' AS DATE ) FROM LettingUnitBookings ) AS lub1, ( SELECT F_LU_ID, End AS date_time FROM LettingUnitBookings UNION SELECT F_LU_ID, CAST( '1000-01-01' AS DATE ) FROM LettingUnitBookings ) AS lub2 WHERE lub2.date_time <= lub1.date_time AND lub2.F_LU_ID = lub1.F_LU_ID GROUP BY lub1.F_LU_ID, lub1.date_time ) Slots JOIN LettingUnits lu ON lu.ID = Slots.F_LU_ID WHERE Slots.AvailableDays >= @WindowSize AND ( DATEDIFF( Slots.EndOfSlot, @EndOfWindow ) >= @WindowSize AND DATEDIFF( @StartOfWindow, Slots.StartOfSlot ) >= @WindowSize ) OR ( DATEDIFF( @EndOfWindow, Slots.StartOfSlot ) >= @WindowSize AND DATEDIFF( Slots.EndOfSlot, @StartOfWindow ) >= @WindowSize ) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload