sql server - 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
)