Artful Common Queries

Find available reservation periods

from the Artful Common Queries page

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.

Last updated 16 Aug 2024