Creating SQL to produce list of consecutive free time slots

Given the table from the Excel attachment.

The table contains

* all time slots (open or booked)

* in a granularity of 30 minutes/slot

* of tables (4)

* on 3 days

* between the slots 09:00 a.m. and 6:00 p.m. (09:00 - 18:00)

Slots are booked (= not available) when the field 'company_id' is not null

My problem: Given a choice of 3 needed time spans

30 minutes

60 minutes

90 minutes

I need a list of all start times (over all days) per table or grouped over all tables per day to which the required time span can be added without conflicting with existing reservations

Example:

(1) Day 1, Table 1, 9 a.m. - FREE

(2) Day 1, Table 1, 9:30 a.m. - BOOKED

(3) Day 1, Table 1, 10.00 a.m. - FREE

(4) Day 1, Table 1, 10:30 a.m. - FREE

(5) Day 1, Table 1, 11:00 a.m. - FREE

(6) Day 1, Table 1, 11:30 a.m. - BOOKED

Needing 30 mins., the list would product the slots 1, 3, 4, 5

Needing 60 mins., the list would produce the slots 3, 4

Needing 90 mins., the list would product the slot 3

There is an example on how it can be solved here:

[login to view URL] (Solution #2)

But I cannot quite get it to work for my table.

You may fill and use the column ‚id‘ as you see fit to produce a solution (the column is not needed elsewhere).

MySQL SQL

Project ID: #8798964

About the project