mysqlfreeslotstimeslots

SQL - Find free slots for booking system


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:

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!


Solution

  • 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