CODEWITHSUNDEEP
phpmysqlsql-optimization
Ali
Ali

Reputation: 7493

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

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

Upvotes: 3

Views: 321

Answers (3)

Flo
Flo

Reputation: 1671

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

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191819

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?

Upvotes: 2

Alessandro
Alessandro

Reputation: 1336

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

Upvotes: 1

Related Questions