Search code examples
sql-server-2008t-sqlcensus

Calculating patient census by hour

I am trying to build a query that calculates number of patients in the emergency room by hour. I have each patients arrival and departure times. I tried building a boolean style query but all it did was give me the arrivals by hour using this logic

SELECT MRN,
   ,CASE WHEN CAST(EDArrival AS TIME) between  '00:00:00.000' and '00:59:59.000' then 1 else 0 end as Hour0
   ,CASE WHEN CAST(EDArrival AS TIME) between  '01:00:00.000' and '01:59:59.000' then 1 else 0 end as Hour1
   ,CASE WHEN CAST(EDArrival AS TIME) between  '02:00:00.000' and '02:59:59.000' then 1 else 0 end as Hour2
FROM EDArrivals

WHERE EDArrival between '2012-06-01' and '2013-07-01'

I was thinking maybe the query could place a column for each hour with a 1 or 0 in they were in the ED during those hours. What I ultimately want to get to is average patients in the ED by hour over the course of a year. If anyone can think of an easier method I would greatly appreciate the help.

Thank you

Solution

  • I managed to create an example of my comment in SQLFiddle.

    http://sqlfiddle.com/#!6/5234e/6

    It's similar to JNK answer (hey, I commented first!)

    By the way, creating that table variable will not be great, consider keeping a domain table with the hours.

    If do you need performance consider also persisting the date part values. Evaluating them for each row is a performance killer.

    Also take care with null departures date times and patients staying at midnight.