Challenge: Generate availability periods (holes) in between hotel bookings

Dev Shed Forums Sponsor:

September 19th, 2012, 02:33 PM

LucasLC

Registered User

Join Date: Sep 2012

Posts: 2

Time spent in forums: 33 m 28 sec
Reputation Power: 0

Challenge: Generate availability periods (holes) in between hotel bookings

I have a hotel bookings table with room_id, checkin_date and checkout_date for each booking record.
I want to generate a table with the start and end dates of the holes in between for each room_id.
P.S. Bookings might overlap a few days (over-bookings), both on one side (start or end) or both sides (start and end).

Does anyone know a way to do this in a single SQL query? The only input parameters would be cal_start_date, cal_end_date (in between those dates we would generate the holes)

Simplified example:

BOOKINGS TABLE
checkin checkout
Sep 14 Sep 20
Sep 19 Sep 21
Sep 28 Sep 29

INPUT PARAMETERS
cal_start_date cal_end_date
Sep 1 Sep 30

QUERY OUTPUT
start end
Sep 1 Sep 14
Sep 21 Sep 28
Sep 29 Sep 30

September 19th, 2012, 05:43 PM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Your example appears to show overlapping periods. Is this intentional?

September 20th, 2012, 12:22 AM

LucasLC

Registered User

Join Date: Sep 2012

Posts: 2

Time spent in forums: 33 m 28 sec
Reputation Power: 0

Quote:

Originally Posted by cafelatte

Your example appears to show overlapping periods. Is this intentional?

Yes, intentional, to show that overbookings can be there as well (booking1 and booking2 are overlapping)

P.S. I have already designed the query that merges overbookings AND "2 perfectly connected bookings" (however, does NOT work for more than 2, such as: 3,4,5..n perfectly connected bookings):

----
Select distinct

(
select min(bookings2.checkin)
from bookings as bookings2
where bookings2.check_in<=bookings1.check_in
and bookings2.check_out>=bookings1.check_in
and bookings2.room_id=bookings1.room_id
) as merged_checkin
,
(
select max(bookings2.checkout)
from bookings as bookings2
where bookings2.check_out>=bookings1.check_out
and bookings2.check_in<=bookings1.check_out
and bookings2.room_id=bookings1.room_id
) as merged_checkout
,
bookings1.room_id as room_id

from bookings as bookings1
----

but then how to find the holes?

September 20th, 2012, 08:27 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Perhaps you can adapt this partial solution to your purposes. It assumes a calendar utility table holding all dates (`dt`) that could ever be required...

Code:

SELECT a.dt Start
     , MIN(c.dt) End 
  FROM 
     ( SELECT dt
         FROM calendar c 
         LEFT 
         JOIN bookings b 
           ON c.dt >= b.start_date 
          AND c.dt < b.end_date
        WHERE c.dt BETWEEN '2012-09-01' AND '2012-09-30'
          AND b.item_id IS NULL
     ) a 
  LEFT 
  JOIN 
     ( SELECT dt
         FROM calendar c 
         LEFT 
         JOIN bookings b 
           ON c.dt >= b.start_date 
          AND c.dt < b.end_date
        WHERE c.dt BETWEEN '2012-09-01' AND '2012-09-30'
          AND b.item_id IS NULL
     ) b
    ON a.dt = b.dt + INTERVAL 1 DAY
  LEFT 
  JOIN 
     ( SELECT dt
         FROM calendar c 
         LEFT 
         JOIN bookings b 
           ON c.dt >= b.start_date 
          AND c.dt < b.end_date
        WHERE c.dt BETWEEN '2012-09-01' AND '2012-09-30'
          AND b.item_id IS NULL
     ) c 
    ON a.dt <= c.dt
  LEFT 
  JOIN 
     ( SELECT dt
         FROM calendar c 
         LEFT 
         JOIN bookings b 
           ON c.dt >= b.start_date 
          AND c.dt < b.end_date
        WHERE c.dt BETWEEN '2012-09-01' AND '2012-09-30'
          AND b.item_id IS NULL
     ) d 
    ON c.dt = d.dt - INTERVAL 1 DAY
 WHERE b.dt IS NULL 
   AND c.dt IS NOT NULL
   AND d.dt IS NULL
 GROUP 
    BY a.dt;

+------------+------------+
| Start      | End        |
+------------+------------+
| 2012-09-01 | 2012-09-13 |
| 2012-09-21 | 2012-09-27 |
| 2012-09-29 | 2012-09-30 |
+------------+------------+

Challenge: Generate availability periods (holes) in between hotel bookings

Developer Shed Advertisers and Affiliates