#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Your example appears to show overlapping periods. Is this intentional?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    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?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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 |
    +------------+------------+

IMN logo majestic logo threadwatch logo seochat tools logo