We have this database schema to work with: http://sqlzoo.net/wiki/Guest_House
The issue is a common request as such:
A customer wants a double room for 7 consecutive nights at some time between a starting date and an ending date
The output should be like this:
+-----+------------+
| id | MIN(i) |
+-----+------------+
| 210 | 2016-11-19 |
| 304 | 2016-11-19 |
+-----+------------+
The way we approached the problem is to try and do a diff. Simply to take the difference between booking date of next instance with checkout date of previous instance.
The difficulty is to define which two pairs make the previous-next pair, because when you are doing a cross self-join you are taking all the possible pairs!
We are aware that the way to treat such cases is to use depended subqueries such as here: https://stackoverflow.com/a/35615532/720484
However following this approach we ended up with a huge subquery as shown in this sql script:
select b1.room_no, ADDDATE(b2.booking_date, b2.nights) as date_avail
from
(select booking_id, booking_date, room_no, nights from booking where room_type_requested = 'double') as b1 INNER JOIN
(select booking_id, booking_date, room_no, nights from booking where room_type_requested = 'double') as b2
ON b1.booking_id <> b2.booking_id and b1.room_no = b2.room_no
where b1.booking_date >= ADDDATE(b2.booking_date, b2.nights)
and DATEDIFF(b1.booking_date, b2.booking_date) = (
select MIN(DATEDIFF(bi1.booking_date, bi2.booking_date)) as min_date_dist
from
(select booking_id, booking_date, room_no, nights from booking where room_type_requested = 'double') as bi1 INNER
JOIN
(select booking_id, booking_date, room_no, nights from booking where room_type_requested = 'double') as bi2
ON bi1.booking_id <> bi2.booking_id and bi1.room_no = bi2.room_no
where bi1.booking_date >= ADDDATE(bi2.booking_date, bi2.nights)
and bi1.booking_id = b1.booking_id
group by bi1.booking_id
)
and b1.booking_date - ADDDATE(b2.booking_date, b2.nights) >= 7 /* nights avail */
and ADDDATE(b2.booking_date, b2.nights) between '2016-11-03' and '2016-12-19'
order by date_avail, b1.room_no
How could you write this script more elegantly?
