Creating SQL to produce list of consecutive free time slots

En curso Publicado Oct 30, 2015 Pagado a la entrega
En curso Pagado a la entrega

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

Nº del proyecto: #8798964

Sobre el proyecto

3 propuestas Proyecto remoto Activo Oct 30, 2015

3 freelancers están ofertando un promedio de €21 por este trabajo

vishaltile

We are SQL professionals and we will deliver you in less time with quality for sure.

€24 EUR en 1 día
(12 comentarios)
3.9
KrishMunot

Will retrieve all relevant information from the excel sheet and display the relevant details from it.

€29 EUR en 2 días
(0 comentarios)
0.0
prodanecivan

Пропозиція ще не була подана

€10 EUR en 3 días
(0 comentarios)
0.0