Patient Census by hour of day

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!

1 REPLY
Senior Apprentice

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 cnt
FROM 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 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

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 dt
GROUP BY 1
ORDER 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