SQL - Find free slots for booking system

Question

I got a simple mysql table for already booked rooms. Here the sql structure:

CREATE TABLE Rooms (
   `ID` int, `Description` varchar(50)
);

INSERT INTO Rooms values
(123, 'Room in Frankfurt'),
(234, 'Room in Wiesbaden'),
(245, 'Room in Darmstadt');

CREATE TABLE Bookings (
   `ID` int, `StartTime` datetime, `EndTime` datetime, `post_ID` int
);

INSERT INTO Bookings
    (`ID`, `StartTime`, `EndTime`, `post_ID`)
VALUES
    (1, '2018-01-05', '2018-04-05', 123),
    (2, '2018-08-01', '2018-10-01', 123),
    (3, '2019-02-01', '2019-06-01', 123),
    (4, '2018-02-01', '2018-06-01', 234),
    (5, '2018-08-01', '2018-09-01', 294),
    (6, '2018-09-01', '2018-11-30', 234),
    (7, '2018-11-01', '2018-12-30', 294)
;

In this table we can see all bookings for all rooms we have. My problem is to find a SQL Query to find free slots for a room. The user can give these parameters:

a date for the earliest check-in (ex: 2018-10-01)
a date for the latest check-in (ex: 2018-10-15)
a maximum period in months (example: 3 months)

So the user needs a room for 3 months, starting from 01-15. October 2018.

How can i do this? I really don't get it.

Thanks!

logically you should have another table with all rooms? `post_ID` is it `room_ID` ? — Alex, Oct 10 '18 at 12:28
yes there is a table for rooms. Just post_ID and room_name and some other informations for the room - not relevant in this case. :) — endcoreCL, Oct 10 '18 at 12:49
@endcoreCL if `rooms` is irrelevant - than your data set is irrelevant too. All your rooms are available starting from `2018-10-01` - what is the reason to provide data sample with only positive scenarios? just do `SELECT * FROM rooms` ;-) is it still irrelevant? — Alex, Oct 10 '18 at 12:53
Your data sample has conflict. you have room `294` booked but does not exist in `Rooms` table. — Alex, Oct 10 '18 at 14:16

Alex · Accepted Answer · 2018-10-10T14:13:30.867

1

I assume that you have some kind of Rooms table

http://sqlfiddle.com/#!9/fe977/1

SELECT r.*
FROM rooms r
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND (
     (b.StartTime <= '2018-10-01'
   AND b.EndTime >= '2018-10-01')
     OR
     (b.StartTime >= '2018-10-01'
   AND b.StartTime <= '2018-10-15')
   )     
WHERE b.id IS NULL

UPDATE I am still not sure if I've got your goal. But here is another approach. If you want start date to be flexible, I would recommend to set calendar table for all dates in the year. This will allow to avoid unnecessary calculations when you run query.

Here is a fiddle: http://sqlfiddle.com/#!9/29926/3

SELECT r.*,
c.date
FROM rooms r
LEFT JOIN calendar c
ON c.date BETWEEN '2018-10-01' AND '2018-10-15'
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND (
     (b.StartTime <= c.date
   AND b.EndTime >= c.date)
     OR
     (b.StartTime >= c.date
   AND b.StartTime <= (c.date + INTERVAL 3 MONTH))
   )     
WHERE b.id IS NULL

UPDATE 2 Combining with @Strawberry answer I guess we can modify query to:

http://sqlfiddle.com/#!9/29926/5

SELECT r.*,
c.date
FROM rooms r
LEFT JOIN calendar c
ON c.date BETWEEN '2018-10-01' AND '2018-10-15'
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND b.StartTime <= (c.date + INTERVAL 3 MONTH)
   AND b.EndTime >= c.date     
WHERE b.id IS NULL

edited Oct 10 '18 at 14:13

answered Oct 10 '18 at 12:39

Alex

16,739
1
28
51

There's more query here than is strictly necessary :-( – Strawberry Oct 10 '18 at 12:44
@Strawberry that is possible, please share your version :-) – Alex Oct 10 '18 at 12:45
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. – Strawberry Oct 10 '18 at 12:47
Thanks for your reply, but i think this is not correct at all. The room 123 is already free for this search. The last booking is ending on 2018-10-01 and there is a latest check-in at 2018-10-15. And i need a check if the not booked room is free for the given period (ex 3 months). – endcoreCL Oct 10 '18 at 12:47
@endcoreCL if you want to count `endDate` as `room is free` you can change `AND b.EndTime > '2018-10-01')` that is simple change. **given period** was `2018-10-01 - 2018-10-15` but if I get it wrong - you can change it to whatever you want - the logic will remain the same. – Alex Oct 10 '18 at 12:50
with period i mean: the user has a early and latest check-in (in this case there 15 possible check-in days) and he need the room for 3 months. so when there is another booking 2 months later, the room is for this search case not free. – endcoreCL Oct 10 '18 at 12:52
thanks for your update alex. this looks very good and i think i can work with it. i will test it tomorrow! – endcoreCL Oct 10 '18 at 16:10

score 0 · Answer 2 · answered Oct 10 '18 at 13:34

First, let us see the empty intervals per room:

select room_ID, a.EndTime, b.StartTime
from Bookings a
join (select c.StartTime
      from Bookings c
      where c.room_ID = a.room_ID and 
      not exists (select 1
                  from Bookings d
                  where d.room_ID = c.room_ID and d.StartTime > a.EndTime and d.StartTime < c.StartTime)) b;

This should be unioned with the interval between now() and the StartTime of the very first element. This way you will have a result from which you can derive the answers to your questions. I do not intend to implement the solution for your homework, this answer is meant to be a starting point for a solution.

Strawberry · Answer 3 · 2018-10-10T14:40:54.687

Supplied criteria:

     1.  a date for the earliest check-in (ex: 2018-10-01)
     2.  a date for the latest check-in (ex: 2018-10-15)
     3.  a maximum period in months (example: 3 months)

Here's a solution that considers criteria 1 and 3 only...

SELECT r.*
  FROM rooms r
  LEFT 
  JOIN bookings b
    ON b.post_id = r.id
   AND b.starttime < '2018-10-01' + INTERVAL 3 MONTH
   AND b.endtime > '2018-10-01'
 WHERE b.id IS NULL;
+------+-------------------+
| ID   | Description       |
+------+-------------------+
|  123 | Room in Frankfurt |
|  245 | Room in Darmstadt |
+------+-------------------+

But your real question seems to be:

Which rooms have '3-month-availability' within a 45-day period commencing 1st October. This is a slightly different (and I think more complicated) problem.

Now I see what you've meant. Solutions often much simpler than we expect. — Alex, Oct 10 '18 at 14:14

SQL - Find free slots for booking system

3 Answers3