18  Occupancy

If you’re in the business of working with objects that can be occupied or available, a metric that you’ll likely need to calculate is occupancy. This is a measure of how much time an object is occupied over a given time period. For example, if you have a hotel, you might want to know how many rooms are occupied on a given day. If you have a parking lot, you might want to know how many parking spots are occupied not only on a given day but a given hour.

If you’re given the data in a form where the granularity matches the granularity of the occupancy (e.g. the availability of each room on each day), you’re probably fine. But usually the data will come in the form of date intervals. This chapter deals the latter type of data.

19 Hourly occupancy - SQL Server

If you’re using SQL Server, a quick solution is using Itzik Ben-Gan’s Tally Table as described here. The idea is splitting the date intervals into smaller units, such as hours, to match the granularity of the occupancy. It’s fast and will get the job done.

20 Hourly occupancy - everyone else

Luckily, calculating occupancy is the same as creating a factless fact table and running a count. The idea is basically the same as creating a factless fact table so this recipe is an extension of the model.

I’ve tried porting that code to SQLite but the query timed out. Instead, we’re going to join an hourly calendar table to our dataset.

WITH calendar AS (
    SELECT
        datetime('2024-01-01 00:00:00') + INTERVAL hour HOUR AS hour_start
    FROM generate_series(0, 24 * 365 - 1) AS hour
)
SELECT
    c.hour_start,
    COUNT(*) AS occupied_count
FROM calendar c
LEFT JOIN occupancy o
    ON c.hour_start BETWEEN o.start_time AND o.end_time
GROUP BY c.hour_start
ORDER BY c.hour_start;