Calculating inpatient occupancy using a calendar table

All data in this post is fake data, generated artificially.

Hospital beds are an expensive resource that isn’t easy to flex. Intelligence about occupancy is useful for operational management of the bed base. The traditional way to approach this is to create a “bed census”. That is, a data set which shows how many patients were in beds at midnight each day.

There are a variety of ways of going about this. It’s possible to cook something up from an episodes extract in Excel, and I’ve also seen solutions that use SQL loops. For R users, there’s the patientcounter package created by John Mackintosh.

But my preferred solution is a set-based SQL approach using a calendar table (i.e. a reference table with one row for each calendar date).

The fundamental task is to count up the number of days in which a patient is in a bed at midnight. That suggests that we should aim to create a table in which each row represents a “bed-day”. That is, a patient episode starting at 10am on 1^st January and ending at 4pm on the 4^th January should correspond to three rows in the new table: one for midnight on 1^st January, one for midnight on 2^nd January, and one for midnight on 3^rd January. Then we can use GROUP BY to count up the number of patients in bed at midnight on each day.

Let’s start by looking at all the episodes in which the patient was in hospital for part of the first week in January.

SELECT TOP 100
    ipe.EpisodeID
        ,ipe.StartDate
        ,ipe.EndDate

FROM episode ipe

WHERE ipe.EndDate >= '2020 JAN 01'
AND ipe.StartDate < '2020 JAN 08'

ORDER BY ipe.StartDate
        ,ipe.EndDate

We can then create our “bed-day” table by joining the episodes table to the calendar table. The join is written so that every row in the calendar table with a date between the start date and end date of the episode will match. Note that in the results, we now have one row for every midnight that the patient was an inpatient.

It’s also worth noting I’m exploiting the default behaviour of T-SQL here. Because the dates in the lkp_calendar table do not have a time, T-SQL treats the date as though it represents midnight on the day in question.

SELECT TOP 100 
    ipe.EpisodeID
        ,ipe.StartDate
        ,ipe.EndDate
        ,census.[Date] AS 'Census Date'

FROM episode ipe
LEFT JOIN lkp_calendar census 
    ON census.[Date] >= ipe.StartDate 
        AND census.[Date] < ipe.EndDate

WHERE ipe.EndDate >= '2020 JAN 01'
AND ipe.StartDate < '2020 JAN 08'

ORDER BY ipe.StartDate
        ,ipe.EndDate
        ,census.[Date]

Finally, we can calculate the overall occupancy on each day by using a GROUP BY. I’ve swapped the WHERE clause to return just the census dates we’re interested in.

SELECT census.[Date]
      ,COUNT(*) AS 'Patients in Bed at Midnight'

FROM episode ipe
LEFT JOIN lkp_calendar census 
    ON census.[Date] >= ipe.StartDate 
        AND census.[Date] < ipe.EndDate

WHERE census.[Date] >= '2020 JAN 01'
AND census.[Date] < '2020 JAN 08'

GROUP BY census.[Date]

ORDER BY census.[Date]

SELECT census.[Date]
        ,spec.Meaning AS 'Specialty'
        ,COUNT(*) AS 'Patients in Bed at Midnight'

FROM episode ipe
LEFT JOIN lkp_calendar census 
    ON census.[Date] >= ipe.StartDate 
        AND census.[Date] < ipe.EndDate
LEFT JOIN lkp_mainspecialty spec
    ON spec.Code = ipe.MainSpecialtyCode

WHERE census.[Date] >= '2020 JAN 01'
AND census.[Date] < '2020 JAN 08'
AND spec.Code IN (100, 101, 110, 130)

GROUP BY census.[Date]
            ,spec.Meaning

ORDER BY spec.Meaning 
            ,census.[Date]
            

SELECT census.[Date]
        ,COUNT(*) AS 'Patients in Bed at Midnight'
        ,SUM(CASE WHEN CAST(ipe.StartDate AS date) = census.[Date] - 1
            THEN 1
            ELSE 0
            END) AS 'Admissions'
        ,SUM(CASE WHEN CAST(ipe.EndDate AS date) = census.[Date] 
            THEN 1
            ELSE 0
            END) AS 'Discharges'

FROM episode ipe
LEFT JOIN lkp_calendar census 
    ON census.[Date] >= ipe.StartDate 
        AND census.[Date] < ipe.EndDate

WHERE census.[Date] >= '2020 JAN 01'
AND census.[Date] < '2020 JAN 08'

GROUP BY census.[Date]

ORDER BY census.[Date]

WITH calendarwithtimes AS 
    (
    SELECT DATEADD(hour, clock.hr, census.[Date]) AS 'Census Date and Time'

    FROM lkp_calendar census
    LEFT JOIN (VALUES (0), (6), (12), (18)) AS clock(hr)
      ON 1 = 1 
    )

SELECT census.[Census Date and Time]
        ,COUNT(*) AS 'Patients in Bed'

FROM calendarwithtimes census
LEFT JOIN episode ipe
    ON census.[Census Date and Time] >= ipe.StartDate 
        AND census.[Census Date and Time] < ipe.EndDate

WHERE census.[Census Date and Time] >= '2020 JAN 01'
AND census.[Census Date and Time] < '2020 JAN 08'

GROUP BY census.[Census Date and Time]

ORDER BY census.[Census Date and Time]