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 |