Need to see if a range of dates overlaps another range of dates in sql

Question

I have a table which stores bookings of rooms, the schema is:

ID | ROOM_ID | CHECK_IN_DATE | CHECK_OUT_DATE | USER_ID

I need to run a search query for rooms which are available/unavailable between a set range of dates.

Also keep in mind that there exists another table which holds dates when the room is prebooked and its in the format:

ROOM_ID | DATE

SO I need to run a query which looks for rooms available within a set range, How would I formulate the query? I'm using MySQL here.

---edit---

Theres also a Rooms table of the schema:

ID | ROOM DETAILS etc

The unavailability/prebooked dates table basically holds sporadic single dates, each date in the unavailability table refers to a date when the room for some reason cannot be booked eg: maintenance etc

The meaning of the prebooked table is unclear. What's the meaning of the `DATE` field? What range is applied? — Alessandro, Feb 23 '11 at 14:25
Also, this table structure assumes that rooms do not exist unless they have either been prebooked or booked. Is there perhaps a rooms table? — eric.christensen, Feb 23 '11 at 14:36

score 2 · Accepted Answer · answered Feb 23 '11 at 14:22

SELECT
   ROOM_ID
FROM
   Rooms r
   LEFT JOIN Bookings b ON (
      r.ROOM_ID = b.ROOM_ID
      AND b.CHECK_IN_DATE > '$MAX_DATE'
      AND b.CHECK_OUT_DATE < '$MIN_DATE'
   )

I'm not sure how pre-booked rooms factors in as there is no date range. Do pre-booked rooms also get an entry on bookings or not?

score 1 · Answer 2 · answered Feb 23 '11 at 14:22

1

SELECT id FROM rooms WHERE id NOT IN (
   SELECT room_id FROM bookings 
   WHERE check_in_date < 'end date' AND check_out_date > 'start date'
);

answered Feb 23 '11 at 14:22

Alessandro

1,336
8
15

You have the < and > reversed. 2011-02-23 < 2011-02-24 , but this means the room is unavailable if the check out date is on the 25th. Same with checkout (2011-02-23 > 2011-02-22, but the people have not checked out!) – Explosion Pills Feb 23 '11 at 14:26
Actually, that should work. I consider a room unavailable if there's at least a booking ending after the beginning of my desired period and starting before the end of it. – Alessandro Feb 23 '11 at 14:31

score 0 · Answer 3 · answered Feb 23 '11 at 14:22

there are like 5 possible ways:

---s-----e---

s-e----------
--s-e--------
-----s-e-e---
--------s-e--
----------s-e

s = start / e=end firste line is your database set and the other are possible searches

only the first and last one are the ones that you want so what you look for is: search.end < entry.start OR search.start > entry.end

Need to see if a range of dates overlaps another range of dates in sql

3 Answers3

Linked