I have hospital patient admission data in Microsoft SQL Server r2 that looks something like this:
PatientID, AdmitDate, DischargeDate
Jones. 1-jan-13 01:37. 1-jan-13 17:45
Smith 1-jan-13 02:12. 2-jan-13 02:14
Brooks. 4-jan-13 13:54. 5-jan-13 06:14
1-jan-13 00:00. 0
1-jan-13 01:00. 0
1-jan-13 02:00. 1
1-jan-13 03:00. 2
To solve this problem, you need a list of date-hours. The following gets this from the admit date cross joined to a table with 24 hours. The table of 24 hours is calculating from
information_schema.columns -- a trick for getting small sequences of numbers in SQL Server.
The rest is just a join between this table and the hours. This version counts the patients at the hour, so someone admitted and discharged in the same hour, for instance is not counted. And in general someone is not counted until the next hour after they are admitted:
with dh as ( select DATEADD(hour, seqnum - 1, thedatehour ) as DateHour from (select distinct cast(cast(AdmitDate as DATE) as datetime) as thedatehour from Admission a ) a cross join (select ROW_NUMBER() over (order by (select NULL)) as seqnum from INFORMATION_SCHEMA.COLUMNS ) hours where hours <= 24 ) select dh.DateHour, COUNT(*) as NumPatients from dh join Admissions a on dh.DateHour between a.AdmitDate and a.DischargeDate group by dh.DateHour order by 1
This also assumes that there are admissions on every day. That seems like a reasonable assumption. If not, a calendar table would be a big help.