Get available room between two dates when there are multiple category in one booking

Question

My problem is I'm lost as to what to do when getting the available room between 2 dates when the bookings can have multiple room type in one booking.

I have 3 tables:

reservedRoom

    create table reservedRoom (
bookingID int ,
roomID int NOT NULL,
n_person int,
)



bookingID   roomID  n_person
2            1          2
2            2          2
2            3          3
2            4          3
2            5          3

room

create table room (
roomID int NOT NULL PRIMARY KEY,
descriptions int NOT NULL,
rate int,
category varchar(30),
)
roomID  descriptions    rate    category
1              2        2000    standard
2              2        2000    standard
3              2        2000    standard
4              2        2000    standard
5              2        2000    standard
6              2        2000    standard
7              2        2000    standard
8              2        2000    standard
9              2        2500    quad
10             2        2500    quad
11             2        2500    quad
12             2        2500    quad
13             2        2500    quad
14             2        3000    family
15             2        3000    family
16             2        3000    family
17             2        3000    family
18             2        8000    King
19             2        8000    King
20             2        8000    King

Booking

create table bookings (
bookingID int NOT NULL PRIMARY KEY,
clientID int NOT NULL,
checkIndate DATETIME,
checkOutDate DATETIME,
roomsCount int,
numNights int,
bookExpire DATETIME,
)

   bookingID    clientID    checkIndate checkOutDate    roomsCount  numNights   bookExpire
1                 1        2018-02-08   2018-02-09      3             2     2018-02-11 
2                 2        2018-02-08   2018-02-09      5             2     2018-02-11 
3                 3        2018-02-08   2018-02-09      3             2     2018-02-11 
4                 4        2018-02-08   2018-02-09      3             2     2018-02-11 
5                 5        2018-02-08   2018-02-09      3             2     2018-02-11

I tried this code but I don't know what to do from here.

 $availableRooms = booking::where(function($query) use ($checkInDate_1, $checkOutDate_1)
                {
                  $query->where(function($query) use ($checkInDate_1, $checkOutDate_1){
                      $query->whereDate('bookings.checkOutDate', '>=', $checkInDate_1);
                      $query->whereDate('bookings.checkOutDate', '<=', $checkOutDate_1);
                  });
                })
              ->whereDate('bookings.bookExpire', '>',$checkOutDate_1)
              ->get();

My problem is that how do i group the rooms by bookingID and integrate that group to the query to get the rooms available at checkIn and checkOut.

Desired Result for date 2018-02-08 - 2018-02-09

standard room - 3 Rooms available
quad room - 5 rooms available
family room - 4 rooms available
king room - 3 rooms available

can you please write your sql query with your problem statement rather that hibernate ? — Ankit Agrawal, Feb 08 '19 at 06:44

score 1 · Answer 1 · answered Feb 08 '19 at 06:58

Here is an example of how to go about with the query

First step, find out the current number of a rooms, grouped by category for the bookings

After that subtract the value by category with the total rooms grouped by category from the room table

   select x.category
          ,count(x.roomId) - case when isnull(max(booked_rooms.cnt))=1 then
                                       0
                                  else max(booked_rooms.cnt)
                                  end as available_rooms
     from room x
left join ( select c.category /* Find out the rooms bny category which are booked within the dates*/
                  ,count(c.roomID) as cnt
              from bookings a
              join reservedRoom b
                on a.bookingID=b.bookingID
              join room c
                on b.roomID=c.roomID
            where a.checkIndate between '2018-02-08' and '2018-02-09' 
            group by c.category
           )booked_rooms
        on x.category=booked_rooms.category
 group by x.category

Here is a dbfiddle link

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8a91f2486bac25bda3f46b6ae3cddf8c

Salman A · Accepted Answer · 2019-02-08T10:25:21.667

1

You just need a simple range intersection query. This should do it:

SELECT category, COUNT(*)
FROM room
WHERE NOT EXISTS (
    -- room is booked on the requested dates (...not)
    SELECT 1
    FROM reservedRoom
    JOIN bookings ON reservedRoom.bookingID = bookings.bookingID
    WHERE reservedRoom.roomID = room.roomID
    AND '2018-02-09' > checkIndate
    AND '2018-02-08' < checkOutDate
)
GROUP BY category

edited Feb 08 '19 at 10:25

answered Feb 08 '19 at 07:08

Salman A

262,204
82
430
521

score 1 · Answer 3 · answered Feb 08 '19 at 07:16

1

Your sql request will be

select r.category, count(*) as [count]
from room as r
    left join reservedRoom as rr
        on r.roomID = rr.roomID
    left join bookings as b
        on rr.bookingID = b.bookingID
            and ((b.checkIndate >= @DateFrom and b.checkIndate < @DateTo)
                or (b.checkOutDate > @DateFrom and b.checkOutDate <= @DateTo)
            )
where b.bookingID is null
group by r.category

answered Feb 08 '19 at 07:16

Alexey Chuksin

90
4

1

The rule for overlaps is very simple; Event A overlaps Event B if Event A starts before Event B ends, and ends after Event B starts. There is no 'or' about it. – Strawberry Feb 08 '19 at 07:35

Get available room between two dates when there are multiple category in one booking

3 Answers3