SQL query to search for room availability

Question

The follow tables I have are:

 CUSTOMERS (CustomerID, firstname, surname etc)
 ROOMS (RoomID, RoomType, Description)
 BOOKING (BookingID, CustomerID, Arrival Date, Departure Date, number of people, total cost)
 ROOMS_BOOKED (BookingID, RoomID)

The user enters two dates arrival and departure. Then checks for room availability. There is a room booked at the moment which I have inserted into the database myself. So the BOOKING table now includes BookingID = 1, CustomerID = 1, Arrival Date = 24/03/2015, Departure Date = 26/03/2015, number of people = 1, total cost = 40.00. Then in the ROOMS_BOOKED table I Have BookingID = 1 and RoomID = 2 which is a standard room.

I am using this sql code -

   SELECT RoomType FROM ROOMS WHERE RoomID NOT IN (SELECT RoomID FROM ROOMS_BOOKED) 

This query is returning rooms that have not been booked, but is not what Im looking for. For example the user selects the same dates that are booked in the database. I therefore want to be able to display all room types on the next page and display an x or something for the particular room type that is booked on those selected dates, something similar to www.galgorm.com.

I need help with this, everytime I post a question about this I never get an answer or some form of help. I have got so far with this hotel system with the design, I now want to be able to finish it off.

Please help me, im sure sometimes you have been in the same situation needing help.

Answer 1

You have the following cases

The user's selected period:
--------[---------]-------
Booking no1 
[-----]-------------------
Booking no2
--------------------[----]
Booking no3
-----[----]---------------
Booking no4
-----------[---]----------
Booking no5
------[-------]-----------
Booking no6
--------------[--------]--
Booking no7
-----[----------------]---

You will have to find which periods cross over. Obviously cases 1 and 2 are free. Cases 3,5,6 are easy to catch as you can search if either the start date of the booking or the end date of the booking is within the user's selection. Cases 4 and 7 you would need to find if either of the user's selection dates would be between the bookings.

So the following finds free rooms:

DECLARE @ArrivalDate AS DATETIME
DECLARE @DepartureDate AS DATETIME

SELECT RoomType 
FROM ROOMS 
WHERE RoomID NOT IN 
(
    SELECT RoomID 
    FROM   BOOKING B
           JOIN ROOMS_BOOKED RB
               ON B.BookingID = RB.BookingID
    WHERE  (ArrivalDate <= @ArrivalDate AND DepartureDate >= @ArrivalDate) -- cases 3,5,7
           OR (ArrivalDate < @DepartureDate AND DepartureDate >= @DepartureDate ) --cases 6,6
           OR (@ArrivalDate <= ArrivalDate AND @DepartureDate >= ArrivalDate) --case 4
)

Answer 2

this query list all rooms and for each room shows if it is available within [Arrival , Departure] dates

SELECT 
    RoomType,
    case when NOT EXISTS (SELECT RoomID 
                  FROM ROOMS_BOOKED rb 
                  JOIN BOOKING b on b.BookingID = rb.BookingID
                  WHERE rb.RoomID = r.Id 
                    and ArrivalDate < 'param Departure Date here'
                    and DepartureDate > 'param Arrival Date here')
        then 1 else 0 end IsAvailable
FROM ROOMS r

Answer 3

here is a sample query which i used for checking the rooms which are not available, the query will return the unavailable rooms

SELECT roomno FROM tbl_ReservedRooms WHERE ((fromdate BETWEEN @checkindate AND @checkoutdate )
OR (todate BETWEEN @checkindate AND @checkoutdate)) 
AND status = 'Active'

Answer 4

SELECT * FROM room rm WHERE rm.Room_id NOT IN (SELECT DISTINCT Room_id FROM booking bk WHERE (bk.arrive_date BETWEEN '$from_date' AND '$to_date') OR (bk.departure_date BETWEEN '$from_date' AND '$to_date') OR (bk.arrive_date<'$from_date' AND bk.departure_date>'$to_date'))