SQL Grabbing hotel room availability between two dates

Question

As the title states, I'm trying to grab all available hotel rooms when a user specifies a check in and check out date. I've made some progress, but I'm struggling to understand the logic behind this process.

Here's what I have:

SELECT r.FLOOR, r.ROOM
FROM BOOKING b, ROOMS r
WHERE TO_DATE('2015-03-28', 'YYYY-MM-DD')
BETWEEN TO_DATE(b.CHECKIN, 'YY-MM-DD') AND TO_DATE(b.CHECKOUT, 'YY-MM-DD')
AND r.ROOMID = b.ROOMID;

This simply returns back all taken rooms on the specified date. (2015-03-28)

How can I change this code to take in two dates, checkin an checkout, while also providing available rooms instead of taken rooms.

Any help is much appreciated!

score 2 · Accepted Answer · answered Mar 31 '15 at 20:18

2

You can use Oracle's wm_overlaps function, which finds overlapping time spans:

select *
from rooms
where roomid not in 
(
  select b.room_id
  from booking b
  where wm_overlaps (
    wm_period(b.checkin, b.checkout),
    wm_period(
      to_date('2014-01-01', 'yyyy-mm-dd'), 
      to_date('2014-01-05', 'yyyy-mm-dd')
    )
  ) = 1
)

In this query, the rooms have no bookings between the both given parameters.

answered Mar 31 '15 at 20:18

guthy

326
1
4

This is exactly what I was looking for! I modified it a bit to better fit the results I need. `select bu.name, r.floor, r.room from rooms r, building bu, roomtypes rt where r.ROOMTYPEID = rt.ROOMTYPEID AND rt.BUILDINGID = bu.BUILDINGID AND roomid not in ( select b.roomid from booking b where wm_overlaps ( wm_period(b.checkin, b.checkout), wm_period( to_date('2015-03-25', 'yyyy-mm-dd'), to_date('2015-04-05', 'yyyy-mm-dd') ) ) = 1 ) ORDER BY r.floor;` – Jon Perron Mar 31 '15 at 20:27

score 0 · Answer 2 · answered Mar 31 '15 at 19:47

0

Try to get list of all rooms and exclude from it booked set, for example

SELECT r.FLOOR, r.ROOM
FROM ROOMS r
EXCEPT
SELECT r.FLOOR, r.ROOM
FROM BOOKING b, ROOMS r
WHERE TO_DATE('2015-03-28', 'YYYY-MM-DD')
BETWEEN TO_DATE(b.CHECKIN, 'YY-MM-DD') AND TO_DATE(b.CHECKOUT, 'YY-MM-DD')
AND r.ROOMID = b.ROOMID;

answered Mar 31 '15 at 19:47

olga

111
3

Interesting... I'm using some examples online, but they all give me syntax errors, im using Oracle, maybe this is the issue? – Jon Perron Mar 31 '15 at 19:54
What kind of error did you get? In Oracle you can try to use MINUS instead EXCEPT – olga Mar 31 '15 at 19:57
Yup switching it to MINUS worked, now how do I get the same result except with two dates? Say I want to do multiple dates, ex: 2015-03-28 to 2015-04-05. – Jon Perron Mar 31 '15 at 20:15

shawnt00 · Answer 3 · 2015-03-31T21:20:18.613

This might be closer. Substitute the parameters (marked with @) as appropriate:

SELECT r.FLOOR, r.ROOM
FROM ROOMS r
WHERE r.ROOMID NOT IN (
    -- exclude rooms where checkin or checkout overlaps with the desired dates
    SELECT r.ROOMID
    FROM BOOKING b
    WHERE (
            b.CHECKIN  BETWEEN TO_DATE(@CHECKIN, 'YY-MM-DD') AND TO_DATE(@CHECKOUT, 'YY-MM-DD')
        OR  b.CHECKOUT BETWEEN TO_DATE(@CHECKIN, 'YY-MM-DD') AND TO_DATE(@CHECKOUT, 'YY-MM-DD')
    )

SQL Grabbing hotel room availability between two dates

3 Answers3