Artful Common Queries

Is a given booking period available?

from the Artful Common Queries page

You rent vacation properties, tracking bookings with a table like this:

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');
SELECT * FROM bookings;
+------+------------+------------+------------+
| ID   | propertyID | startDate  | endDate    |
+------+------------+------------+------------+
|    1 |          1 | 2025-01-01 | 2025-01-15 |
|    2 |          1 | 2025-01-20 | 2025-01-31 |
+------+------------+------------+------------+

You need a query indicating whether a given property is available for a given period of time.

Hotels & property renters usually adopt what is called the 'closed-open' convention for bookings, eg a booking from 22 May through 24 May means you sleep there the nights of 22 and 23 May. To show that property P is available for the desired closed-open period dStart to dEnd, you need to prove there is no booked period for P that overlaps dStart through dEnd. Until you're used to thinking about periods, it's easier to analyse graphically. There are four ways a booked reservation can overlap the desired date range ...

             dStart        dEnd
             |----------------|
        startDate            endDate
        |--------------------------|       
        |------| 
                   |----| 
                            |------|

but there are just two ways a booked reservation can not overlap:

             dStart        dEnd
             |----------------|
       |-----|                |-----|
     |-----|                    |-----|

So the period dStart through dEnd is available if there is no row where ...

!(endDate <= dStart OR startDate >= dEnd)

or equivalently ...

endDate > dStart AND startDate < dEnd

Here is a simple stored procedure for testing the query:

DROP PROCEDURE IF EXISTS isavailable;
DELIMITER |
CREATE PROCEDURE isavailable( iProperty int, dStart date, dEnd date )
SELECT IF( COUNT(1),'No','Yes' ) AS Available
FROM bookings
WHERE propertyID = iProperty 
  AND startDate < dEnd 
  AND endDate > dStart;
|
DELIMITER ;
CALL isavailable(1,'2024-12-27','2025-1-20');
+-----------+
| Available |
+-----------+
| No        |
+-----------+
CALL isavailable(1,'2025-1-10' ,'2025-1-16');
+-----------+
| Available |
+-----------+
| No        |
+-----------+
CALL isavailable(1,'2025-1-16' ,'2025-1-17');
+-----------+
| Available |
+-----------+
| Yes       |
+-----------+
CALL isavailable(1,'2025-1-22' ,'2025-1-23');
+-----------+
| Available |
+-----------+
| No        |
+-----------+
CALL isavailable(1,'2025-1-22' ,'2025-2-2');
+-----------+
| Available |
+-----------+
| No        |
+-----------+
CALL isavailable(1,'2025-2-1' ,'2025-2-2');
+-----------+
| Available |
+-----------+
| Yes       |
+-----------+
CALL isavailable(1,'2024-12-1' ,'2025-2-1');
+-----------+
| Available |
+-----------+
| No        |
+-----------+
1 row in set (0.00 sec)

Last updated 22 May 2024