CODEWITHSUNDEEP
mysqlsql
WayBehind
WayBehind

Reputation: 1697

Get occupancy per every 15-minute slot

We have a room where we can only have XX number of people inside due to current limitations. They come at different times and stay for a different length of time.

I'm trying to get a sum of people currently inside for each 15-min period for a specific date. The server is MySQL 8.0.21 deployed on AWS RDS.

MySQL 8.0 Table: Booking

ID Name PartySize Date BookedFrom BookedTo
1 John 2 2021-01-01 2021-01-01 08:30:00 2021-01-01 10:00:00
2 Mary 4 2021-01-01 2021-01-01 09:00:00 2021-01-01 11:00:00
3 Nick 3 2021-01-01 2021-01-01 10:30:00 2021-01-01 12:30:00

I also have a "helper table" with a time slot for each 24 hour 15-min slot

MySQL Table: Timeslot

ID Time
1 00:00:00
2 00:15:00
3 00:30:00
35 08:30:00
37 09:00:00
38 09:15:00

For example, when I run this query below, I will get the correct count (6 people) for 09:30. What is the most efficient way to get this result for each 15-min slot? Please note that while the BookedTo (datetime field) value may be past midnight, I will always be only making date specific queries.

SELECT 
    t.id, b.date, t.time, SUM(b.partysize) AS total
FROM
    booking b,
    timeslot t
WHERE
    b.date = '2021-01-01' 
        AND t.time = '09:15:00'
        AND b.bookedfrom <= '2021-01-01 09:15:00'
        AND b.bookedto >= '2021-01-01 09:15:00'

Looking for this output for all times (including zeros)

Slot_ID Date Time Total
33 2021-01-01 08:00:00 0
34 2021-01-01 08:15:00 0
35 2021-01-01 08:30:00 2
36 2021-01-01 08:30:00 2
37 2021-01-01 09:00:00 6
38 2021-01-01 09:15:00 6

Upvotes: 2

Views: 319

Answers (3)

forpas
forpas

Reputation: 164174

Use a CTE that returns the specific date for which you want the results, which may not be the same as the column Date in Booking and CROSS join it to Timeslot.
The result should be LEFT joined to Booking and then aggregate:

WITH cte(Date) AS (SELECT '2021-01-01')
SELECT t.ID, t.time, c.Date, 
       COALESCE(SUM(b.PartySize), 0) Total
FROM cte c CROSS JOIN Timeslot t 
LEFT JOIN Booking b
ON b.BookedFrom <= CONCAT(c.Date, ' ', t.time)
AND b.BookedTo >= CONCAT(c.Date, ' ', ADDTIME(t.time, '00:15:00'))
WHERE t.time BETWEEN '08:00:00' AND '17:00:00'
GROUP BY t.ID, c.Date, t.time

Since BookedFrom and BookedTo may not contain the same date, it is not safe to compare only the time parts of the 2 columns to the column time of Timeslot.
This is why all these conditions in the ON clause are needed.

See the demo.

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74680

SELECT
  t.id as slot_id,
  coalesce(b.date, '2021-01-01') as date,
  t.time,
  coalesce(sum(b.partysize),0) as total
FROM
  timeslot t
  LEFT JOIN booking b
  ON t.time >= TIME(b.bookedfrom) AND t.time < TIME(b.bookedto) AND b.date = '2021-01-01'
WHERE
  t.time BETWEEN '08:00:00' AND '17:00:00'
GROUP BY
  t.id,
  b.date,
  t.time

Now, you have some confusing other requirements, but basically this works because multiple rows of timeslot will match to a single row of booking because of the time range expressed.

The confusing requirements are, you say it's only for 8-5pm, but "bookings might extend to the next day".. does it mean that a booking will start at 4pm and finish at 9am the next day? in which case you might need to adjust the AND b.date = '2021-01-01' to be more like AND (DATE(b.bookedfrom) = '2021-01-01' OR DATE(b.bookedto) = '2021-01-01') ...

Upvotes: 2

Mr.P
Mr.P

Reputation: 1257

this query works great ... if you wanna have all dates for all slots .. you will have to have a date table too (ideally within timeslot -> cross join dates and timeslots) ...

use inner join if you wanna get only matching dates and timeslots ..

SELECT t.id as slot_id
, b.date 
, t.time as slot 
, sum(ifnull(party_size,0)) as total
FROM test.timeslot t
LEFT JOIN test.booking b 
ON t.time BETWEEN time(b.booked_from) AND time(b.booked_to)
GROUP BY t.id 
, b.date 
, t.time;

for all timeslots and selected dates:

https://www.db-fiddle.com/f/gLt2Fs8HTDUakMahZHxcTi/0

for matching timeslots and dates:

SELECT t.id as slot_id
, b.date 
, t.time as slot 
, sum(ifnull(party_size,0)) as total
FROM test.timeslot t
JOIN test.booking b 
ON t.time BETWEEN time(b.booked_from) AND time(b.booked_to)
GROUP BY t.id 
, b.date 
, t.time;

Upvotes: 0

Related Questions