Database
Fan

## Patient Census by hour of day

I have a horizontal patient data set. In order words a patient can be admitted to a floor, transfer in / out of a floor, and be discharged. Each of those events are a row of data for each patient.

I want to figure out the census of that floor by the hour of day for a year. I'm new to TD and haven't taken on a task like this in the past so I was hoping to at least get the framework to make this happen.

Example:

Patient 1   Admitted 5/17/2013 800

Patient 1 Discharged 5/19/2013 1100

Patient 2 Transfer In 5/18/2013 700

Patient 2 Transfer Out 5/19/2013 2200

Patient 3 Admitted 5/18/2013 0600

Patient 3 discharged 5/22/2013 0600

So I would have a census of 1 after 800 on 5/17; census of 3 at 700 on 5/18.......

Thanks in advance for helping out a novice!

Junior Contributor

## Re: Patient Census by hour of day

Your narration sounds familiar, it's a variation of a generic problem, a count over a time series :-)

To solve it, you have to elaborate on details and expected result set.

One row for each point in time when the number of patients changes?

That's easy, the query looks basically like this:

`SELECT  ts,  SUM(CASE WHEN typ IN ('Admitted', 'Transfer IN') THEN 1 ELSE -1 END)   OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING) AS cntFROM tab`

Then you have to decide what to do when there are multiple INs and OUTs at the same point in time.

Still one row per input row or aggregated?

`SELECT  ts,  SUM(cnt)   OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING) AS cntFROM (   SELECT ts, SUM(CASE WHEN typ IN ('Admitted', 'Transfer IN') THEN 1 ELSE -1 END) AS cnt   FROM tab   GROUP BY 1   HAVING cnt <> 0  ) dt`

This returns no row when the the number of INs equals the number of OUTs, if you need it comment out the "HAVING cnt <> 0".

But based on your narrative you probably want one row per hour:

`SELECT  ts - (EXTRACT(MINUTE FROM ts) * INTERVAL '1' MINUTE) - (EXTRACT(SECOND FROM ts) * INTERVAL '1' SECOND) AS day_to_hour,   MAX(cnt)FROM (   SELECT     ts,     SUM(cnt)      OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING) AS cnt   FROM    (      SELECT ts, SUM(CASE WHEN typ IN ('Admitted', 'Transfer IN') THEN 1 ELSE -1 END) AS cnt      FROM tab      GROUP BY 1      HAVING cnt <> 0    ) dt ) AS dtGROUP BY 1ORDER BY 1`

What if there were no INs/OUTs for several hours, do you still need one row per hour?

Then it's more complicated and it would help when you mention your TD release :-)

Dieter