CODEWITHSUNDEEP
sqlsql-server
steven dornan
steven dornan

Reputation: 75

SQL query to search for room availability

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.

Upvotes: 6

Views: 24268

Answers (5)

Chandan Gurjar
Chandan Gurjar

Reputation: 35

Conflicting booking happens when both the conditions below are satisfied

  1. Existing Records CheckIn Dates < inComingRequest CheckOut Date

  2. Existing Records Checkout Dates > inComingRequest CheckIn Date

Note: Existing Records CheckinInDates compared against InComing Request's CheckoutDate and viceversa

lets assume incoming req params:

 SET @checkInDate := '2022-09-14'; -- incoming req.checkInDate
 SET @checkOutDate := '2022-09-17'; -- incoming req.checkOutDate

BOOKING table containing some existing bookings :

ExistingBookings

Actual Query to get all conflicting bookings:

select * from BOOKING B where 
B.checkInDate < @checkOutDate
AND B.checkOutDate > @checkInDate

Output (all conflicting bookings):

booking_id  checkInDate checkOutDate
       1    2022-09-1   2022-09-30
       2    2022-09-12  2022-09-15
       3    2022-09-15  2022-09-18
       6    2022-09-14  2022-09-17

booking_id 4 is excluded as the Customer who has existing booking would have just checked out of the hotel when this customer checks in.

booking_id 5 is excluded as the Customer who has existing booking would check in to the hotel just when this customer checks out.

So booking_id 4 and 5 are not actually conflicting with the incoming req

Upvotes: 1

Raj Kumar
Raj Kumar

Reputation: 1

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'))

Upvotes: -1

anandd360
anandd360

Reputation: 306

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'

Upvotes: -1

ASh
ASh

Reputation: 35733

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

Upvotes: 3

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

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
)

Upvotes: 13

Related Questions