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,'2007-1-1','2007-1.15'),
  (2,1,'2007-1-20','2007-1.31'),
  (3,1,'2007-2-10','2007-2-17');
SELECT * FROM bookings; 
+------+------------+------------+------------+
| ID   | propertyID | startDate  | endDate    |
+------+------------+------------+------------+
|    1 |          1 | 2007-01-01 | 2007-01-15 |
|    2 |          1 | 2007-01-20 | 2007-01-31 |
|    3 |          1 | 2007-02-10 | 2007-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         |
+----------------+------------+
| 2007-01-15     | 2007-01-20 |
| 2007-01-31     | 2007-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,'2015-01-10','2015-01-17'),(2,388,'2015-01-30','2015-02-03'),
(3,388,'2015-02-28','2015-03-09'),(4,388,'2015-01-22','2015-01-28'),
(5,388,'2015-02-07','2015-02-28'),(6,388,'2015-03-09','2015-03-30'),
(7,388,'2015-03-30','2015-03-31'),(8,388,'2015-04-05','2015-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         |
+----------------+------------+
| 2015-01-17     | 2015-01-22 |
| 2015-01-28     | 2015-01-30 |
| 2015-02-03     | 2015-02-07 |
| 2015-03-31     | 2015-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.

Return to the Artful Common Queries page