StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Ok, here's a tricky answer for a tricky question...</p> <pre><code>SELECT * FROM property AS p LEFT JOIN ( SELECT property_id, DATEDIFF(MAX(date_end),20110308) AS startblock, DATEDIFF(20110319,MIN(date_start))-1 AS endblock FROM property_booking AS pb WHERE date_start < 20110319 || date_end >= 20110308 GROUP BY property_id HAVING LEAST(startblock,endblock) > 4 ) AS p2 ON p.id = p2.property_id WHERE p2.property_id IS NULL; </code></pre> <p>The subquery selects all the properties that are not eligible. The LEFT JOIN with IS NULL basically works out the exclusion (negation on the ineligible properties) </p> <ul> <li>20110308 is the desired start date -2 days ( because +/-2 day flexibility)</li> <li>20110319 is the desired end date +2 days</li> <li>The number 4 in the <code>HAVING LEAST(startblock,endblock) > 4</code> in twice your +/- number (2*2)</li> </ul> <p>It took me a while to work it out (but your question was interesting and I had time on my hand)</p> <p>I've tested it with edge cases and it worked for all the test cases I've thrown at it...). The logic behind it is a bit odd but a good old pen and paper helped me work it out!</p> <p><strong>Edit</strong> </p> <p>Unfortunately I realized that this will work for most cases but not all... (2 single day bookings at the very beginning and end of the lookup period makes a property unavailable even though it should be available).</p> <p>The problem here is that you have to look up information that's not 'present' in the DB and reconstruct it from what data you have. Check out my comment on your question to see a better way to deal with the problem </p>

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