Determine availability for resources on n date periods

Question

Given the following tables:

resources

| id | name |
| 1  | John |
| 2  | Anna |

bookings

| id | start               | end                | resource_id |
| 1  | 2020-05-29 08:00:00 |2020-05-29 12:00:00 | 1           |

I want to find all resource available for n date periods. I can build a dynamic query using subquery as following:

SELECT name, (
    SELECT COUNT(id) FROM bookings WHERE
        resources.id = bookings.resource_id
        AND '2020-05-29 08:00:00' < `end`
        AND '2020-05-29 09:00:00' > `start` 
) AS 'dp1',
(SELECT COUNT(id) FROM bookings WHERE
        resources.id = bookings.resource_id
        AND '2020-05-29 09:00:00' < `end`
        AND '2020-05-29 10:00:00' > `start` 
) AS 'dp2'
FROM resources

which gives me the following result:

| name | dp1 | dp2 |
| John | 1   | 1   |
| Anna | 0   | 0   |

This does not scale very well. If I want to determine availability for a whole week, from 08-17 that would be 70 subqueries with the current solution.

How can I determine availability for resources for a larger n given date periods more elegantly and efficiently?

Answer 1

I would suggest putting periods in rows rather than in columns. This gives you an opportunity for a pure SQL solution - while you otherwise would need dynamic SQL.

If you are running MySQL 8.0, you can use a recursive query to enumerate the periods, cross join it with the resources table to get all the combinations, and then bring the table with a left join.

Here is a query that would give you the result that you want over a given week. You can adapt the boudaries in the cte as needed.

with period as (
    select '2020-05-22 08:00:00' ts1 
    union all
    select case when hour(ts1) = 16 
        then date_format(ts, '%Y-%m-%d 09:00:00') + interval 1 day
        else ts + interval 1 hour
    end
    from cte    
    where ts < '2020-05-29 17:00:00' 
)
select 
    p.ts period,
    r.name,
    count(b.id) is_reserved
from periods p
cross join resources r 
left join bookings b 
    on  b.start <= p.ts + interval 1 hour 
    and b.end   >= p.ts 
    and b.resource_id = r.id
group by p.ts, r.name

If you are going to run this query frequently, you should consider materializing the results of the cte in a table (that's the concept of a calendar table), that you can then use in your queries.