SQL Query to Search for Room Availability

SQL query to search for room availability

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
)

How to write sql query to check room availability

If the room table lists date ranges when a given room, defined by the id column is occupied and you are trying to determine for each user, defined by the id column in the user table which rooms would be available, then you can't in principle from these two tables; there is a piece missing. As been pointed out, the so-called room table is really a reservation table and does not by necessity enumerate all the rooms in the hotel. That is, there could be a room in the hotel that currently has or has never had a reservation and has no rows in the rooms table. Presumably those rooms would be available to all users. But if we proceed on the assumption that currently every room in the hotel is represented by at least one row in the rooms table, we may proceed. But in the end what we still have is either a question poorly posed or a poor database design.

The logic is: A room.id is available to a user.id if there does not exist a room.id such that:

`user.outdate` >= `room.checkin` and `user.indate` <= `room.checkout`

The SQL:

select distinct u.id, ifnull(r.id, '') as room_id from
user u left join room r on r.id not in (
   select room.id from room where u.outdate >= room.checkin and u.indate <= room.checkout
);

See Db Fiddle

Or if you want to combine all the rooms into one column:

select id, group_concat(room_id) from (
    select distinct u.id, ifnull(r.id, '') as room_id from
    user u left join room r on r.id not in (
        select room.id from room where u.outdate >= room.checkin and u.indate <= room.checkout
    )
) sq
group by id

DB Fiddle

SQL query for room availability check

Modify the query like this,

SELECT x.dt , r.room_cnt,SUM(d.`booking_cnt`) booked,r.room_cnt- SUM(d.`booking_cnt`) available FROM calendar_table x
LEFT JOIN bookings y ON x.dt >= y.`date_from` AND x.dt < y.`date_to`
LEFT JOIN booking_details d ON d.booking_id=y.id and d.room_id='1'
LEFT JOIN rooms r ON r.id= 1
WHERE x.dt BETWEEN now()-interval 3 month AND now() + interval 3 month GROUP BY dt

selecting room type on room availability subquery

You can JOIN the table room like this:

SELECT 
  r.roominv_id, 
  r.room_id 
FROM roominventory AS r
INNER JOIN room AS m ON m.room_id = r.room_id
WHERE m.room_type LIKE '%$roomtype%'
  AND r.roominv_id NOT IN (SELECT b.roominv_id
                           FROM reserve b
                           WHERE NOT (b.chckout < '$chckin'
                                      OR 
                                      b.chckin > '$chckout'));

Room booking sql query

This should do it; if there is a reservation that does not end before or start after the reservation we want, the room is considered busy.

SELECT r.room_id
FROM rooms r
WHERE r.room_id NOT IN (
    SELECT b.room_id FROM bookings b
    WHERE NOT (b.end_datetime   < '2012-09-14T18:00'
               OR
               b.start_datetime > '2012-09-21T09:00'))
ORDER BY r.room_id;

SQLFiddle here.

sql query for hotel room reservation

The Query #2 give you all the free rooms for a given hotel_id and a check in/check out date (the in and out date are included, for 05/n to 10/n you stay 6days)

The query #3 will give you all rented room with the previous parameters.

---

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE TblReservation
    (`reservation_id` int, `hotel_id` int, `room_id` int, `check_in` date, `check_out` date)
;

INSERT INTO TblReservation
    (`reservation_id`, `hotel_id`, `room_id`, `check_in`, `check_out`)
VALUES
    (1, 1, 1, '2017-04-01', '2017-04-02'),
    (2, 1, 1, '2017-04-06', '2017-04-10'),
    (3, 1, 2, '2017-04-01', '2017-04-03'),
    (4, 1, 4, '2017-04-01', '2017-04-10'),
    (5, 2, 5, '2017-04-01', '2017-04-10')
;

CREATE TABLE TblRoom
    (`room_id` int, `hotel_id` int, `room_num` int)
;

INSERT INTO TblRoom
    (`room_id`, `hotel_id`, `room_num`)
VALUES
    (1, 1, 1100),
    (2, 1, 1200),
    (3, 1, 1300),
    (4, 1, 1400),
    (5, 2, 2500)
;

Query 1:

set @hotel_id = 1, @check_in = '2017-04-03', @check_out = '2017-04-05'

Query 2:

select TblRoom.* 
from TblRoom
left join TblReservation
    on TblRoom.hotel_id = TblReservation.hotel_id
        and TblRoom.room_id = TblReservation.room_id
        and TblReservation.check_out >=  @check_in
        and TblReservation.check_in <= @check_out
where
    TblRoom.hotel_id = @hotel_id
    and TblReservation.reservation_id IS NULL

Results:

| room_id | hotel_id | room_num |
|---------|----------|----------|
|       1 |        1 |     1100 |
|       3 |        1 |     1300 |

Query 3:

select 
  TblRoom.*, 
  date_format(check_in,'%Y-%m-%d') check_in, 
  date_format(check_out,'%Y-%m-%d') check_out
from TblRoom
inner join TblReservation
    on TblRoom.hotel_id = TblReservation.hotel_id
        and TblRoom.room_id = TblReservation.room_id
        and TblReservation.check_out >=  @check_in
        and TblReservation.check_in <= @check_out
where
    TblRoom.hotel_id = @hotel_id

Results:

| room_id | hotel_id | room_num |   check_in |  check_out |
|---------|----------|----------|------------|------------|
|       2 |        1 |     1200 | 2017-04-01 | 2017-04-03 |
|       4 |        1 |     1400 | 2017-04-01 | 2017-04-10 |

---

Related Topics