SQL query to find free rooms in hotel

Question

I use a query to find free rooms from hotel DB. I wrote a query which select rooms that not in booking table:

SELECT * FROM room WHERE roomId NOT IN
  (SELECT roomId FROM booking b WHERE STR_TO_DATE('${endDate}', '%m-%d-%Y') <= b.endDate AND
   STR_TO_DATE('${startDate}', '%m-%d-%Y') >= b.startDate);

My booking table looks like:

+-----------+------------+------------+--------+---------+
| bookingId | startDate  | endDate    | roomId | guestId |
+-----------+------------+------------+--------+---------+
|         1 | 2016-03-12 | 2016-03-22 |      1 |       1 |
+-----------+------------+------------+--------+---------+

But if my startDate is 2016-03-10 and endDate is 2016-03-25 - I've got already booked room from 2016-03-12 to 2016-03-22. How I can fix it? I don't need to show room that booked between my dates.

Answer 1

General approach for the problem of finding free rooms in range ($BOOKING_BEGIN <=> $BOOKING_END) would be like:

SELECT 
    rooms.room_id 
FROM 
    rooms 
LEFT JOIN 
     bookings 
     ON (
         bookings.room_id = rooms.room_id AND 
         NOT ( 
             (bookings.begin < $BOOKING_BEGIN and bookings.end < $BOOKING_BEGIN) 
             OR
             (bookings.begin > $BOOKING_END and bookings.end > $BOOKING_END) 
             )
        ) 
WHERE 
    bookings.room_id IS NULL;

Which simply means 'take all the rooms in the hotel, and join them with ones which are already booked. If there's null, that means room is free in a given range (Join didn't find existing booking).

Answer 2

Here is the query that works, and has been tested for all combinations of vacancy before any other. vacancy after anything. Start date before, on, after existing start. End date before, on, after existing end date. Totally straddling outside another booking. And totally within another booking.

select
      r.RoomID
   from
      Room r
         LEFT JOIN 
         ( select 
                 b.RoomID
              from 
                 booking b,
                 ( select @parmStartDate := '2016-01-21',
                          @parmEndDate := '2016-01-23'  ) sqlvars 
              where 
                     b.EndDate >= @parmStartDate
                 AND b.StartDate <= @parmEndDate
                 AND (  timestampdiff( day, b.StartDate, @parmEndDate ) 
                   * timestampdiff( day, @parmStartDate, b.EndDate  )) > 0 ) Occupied
         ON r.RoomID = Occupied.RoomID
   where
      Occupied.RoomID IS NULL;

The sample booking data I created included

BookID  RoomID  StartDate    EndDate
1       1       2016-02-03   2016-02-04
2       1       2016-02-04   2016-02-08
3       1       2016-02-12   2016-02-16
4       1       2016-02-20   2016-02-28

I then tested with the following booking dates and came up with the following valid vacancy vs conflict and already occupied. This test is just for a single room, but obviously applicable for any room in the hotel.

Both dates before anything on file... Room available
2016-01-10 - 2016-01-15 

Both dates after anything on file...  Room available
2016-03-10 - 2016-03-15

Occupied ID 1 -- Same start date
2016-02-03 - 2016-02-04

Occupied ID 2 -- Same start date, but less than existing occupied end date
2016-02-04 - 2016-02-05

Occupied ID 2 -- Same start, Exceeds end occupancy date
2016-02-04 - 2016-02-09

Occupied ID 3 -- Start before, but end date WITHIN existing booking
2016-02-09 - 2016-02-13

Available. The END Date is the START Date of the existing booking 
(Between 2 & 3 booking)
2016-02-09 - 2016-02-12

Occupied ID 3 -- Started within date, but end outside existing booking
2016-02-15 - 2016-02-17

Available. End of existing booking and nothing booked on 2/17
2016-02-16 - 2016-02-17

Occupied ID 3 -- Completely encompasses booking entry
2016-02-11 - 2016-02-17

Occupied ID 4 -- totally WITHIN another entry
2016-02-21 - 2016-02-23

Now, to explain what is going on. I did with a LEFT-JOIN and looking for NULL (ie: no conflict of another booking) which is quite similar to your NOT IN subselect. So I will skip that part.

First, the FROM clause. So I dont have to "declare" variables like a stored procedure, I am doing them IN-LINE via the @parmStartDate, @parmEndDate and assigning the alias sqlvars just for declaration purposes. Since this returns one row, having a Cartesian applied to the booking table is no problem.

from 
   booking b,
   ( select @parmStartDate := '2016-01-21',
            @parmEndDate := '2016-01-23'  ) sqlvars 

Now, the WHERE clause. If your table has years worth of bookings after time, and 100's of rooms, this could get quite large quickly, so I want to pre-start with only those dates where existing bookings would come into place this is the

where 
       b.EndDate >= @parmStartDate
   AND b.StartDate <= @parmEndDate

At a minimum, I only care about those bookings where an existing checkout date is AT LEAST the date you are trying to find availability. Ex: You are looking for a check-in date of July 4th. Why would you even care if someone checked out in Feb, Mar, Apr, etc... So now, how far out do you go... You also only care for those records where the next existing booking has a START Date UP TO the day you would be checking out. So, if checking out July 6th, you don't care about any bookings for July 7th or after. So far, so good.

Now, comes the how do I know if a room is occupied or not. I was having difficulties comparing existing Start Date to looking for dates and was getting false answers, so I had to resort to date math and comparing start to end and end to start, and if the multiplier result was positive, there is a conflict.

AND (  timestampdiff( day, b.StartDate, @parmEndDate ) 
     * timestampdiff( day, @parmStartDate, b.EndDate  )) > 0 ) 

Since we already know we have records within the POSSIBLE date range, this is doing a conflict check in either direction for full outside, inside, conflict left or conflict right. it just works.

You would have to see it to understand it better and this is the query that I ran so you could look at the results for yourself. Just plug in the respective start / end dates you are looking for.

select 
      b.BookID,
      b.RoomID,
      b.StartDate,
      b.EndDate,
      @parmStartDate as pStart,
      @parmEndDate as pEnd,
      (   timestampdiff( day, b.StartDate, @parmEndDate ) 
        * timestampdiff( day, @parmStartDate, b.EndDate  )) <= 0 as Available,
      (   timestampdiff( day, b.StartDate, @parmEndDate ) 
        * timestampdiff( day, @parmStartDate, b.EndDate  )) > 0 as Occupied
   from 
      booking b,
      ( select @parmStartDate := '2016-01-21',
               @parmEndDate := '2016-01-23'  ) sqlvars 

Good Luck...