Given a bookings table where each row specifies one reservation period for one property, find the unbooked periods for a given property:
CREATE TABLE bookings( ID int, propertyID int, startDate date, endDate date ); INSERT INTO bookings VALUES (1,1,'2025-1-1','2007-1.15'), (2,1,'2025-1-20','2007-1.31'), (3,1,'2025-2-10','2025-2-17'); SELECT * FROM bookings; +------+------------+------------+------------+ | ID | propertyID | startDate | endDate | +------+------------+------------+------------+ | 1 | 1 | 2025-01-01 | 2025-01-15 | | 2 | 1 | 2025-01-20 | 2025-01-31 | | 3 | 1 | 2025-02-10 | 2025-02-17 | +------+------------+------------+------------+This looks like the same query pattern as Finding missing numbers in a sequence, and on the strength of that similarity, we offered this solution ... SELECT a.enddate AS 'Available From', Min(b.startdate) AS 'To' FROM bookings AS a JOIN bookings AS b ON a.propertyID=b.propertyID AND a.enddate < b.startdate WHERE a.propertyID=1 GROUP BY a.enddate; +----------------+------------+ | Available From | To | +----------------+------------+ | 2025-01-15 | 2025-01-20 | | 2025-01-31 | 2025-02-10 | +----------------+------------+but as Jeff Groves discovered, this query fails when there are adjacent reservations because of the closed-open method of representing reservations: if you book a hotel room for 22 May through 24 May, the hotel expects you to stay overnight on 22 May and 23 May, but not on 24 May. The query logic has a wrinkle missing in the number sequence problem: an enddate starts an available period if and only if there exists ... - a startdate later than that enddate, and - no startdate equal to it. So add exclusion join logic to the above query: create table bookings( id int, pID int, startdate date, enddate date ); insert into bookings values (1,388,'2025-01-10','2025-01-17'),(2,388,'2025-01-30','2025-02-03'), (3,388,'2025-02-28','2025-03-09'),(4,388,'2025-01-22','2025-01-28'), (5,388,'2025-02-07','2025-02-28'),(6,388,'2025-03-09','2024-03-30'), (7,388,'2024-03-30','2024-03-31'),(8,388,'2024-04-05','2024-04-10'); select a.enddate as 'Available from', min(b.startdate) as 'To' from bookings a join bookings b on a.pid=b.pid and b.startdate>a.enddate left join bookings c on a.pid=c.pid and a.enddate=c.startdate where c.pid is null group by a.startdate, a.enddate; +----------------+------------+ | Available from | To | +----------------+------------+ | 2025-01-17 | 2025-01-22 | | 2025-01-28 | 2025-01-30 | | 2025-02-03 | 2025-02-07 | | 2024-03-31 | 2024-04-05 | +----------------+------------+This is logically equivalent to writing the not-exists condition as a semi-join ... select a.enddate as 'Available from', min(b.startdate) as 'To' from bookings a join bookings b on a.pid=b.pid and b.startdate>a.enddate where not exists( select startdate from bookings where startdate=a.enddate) group by a.startdate, a.enddate;Which will run faster? If we add a covering index for the query ... alter table bookings add index(pid,startdate,enddate);... then under MySQL 5.6 and later, Explain Extended shows the same analysis for the two queries. |