Tricky grouping question

Question

Tricky grouping question

kdbenson (Programmer)

(OP)

24 Aug 09 16:23

Hi,

I have a dataset similar to the following:

Patient_ID Unit Start_Time End_time
1234 3C 5/2/09 12:00 5/5/09 13:00
1234 3FE 5/5/09 13:00 5/5/09 14:00
1234 3FE 5/5/09 14:00 5/7/09 18:00
1234 ICU 5/7/09 18:00 5/16/09 5:00
1234 3C 5/16/09 5:00 5/23/09 7:00
1234 3C 5/23/09 7:00 5/25/09 3:00
1234 3C 5/25/09 3:00 5/28/09 8:00

The data shows the units in which a patient stayed while in the hospital. Start_Time is when they were moved into that unit and End_Time shows when they were moved out of the unit. However, there are also update events and so a patient can have multiple sequential records for the same unit. They can also be moved back into a unit in which they stayed previously. I would like to group this by Patient_ID and Unit, showing the min Start_Time and max End_Time for the time the patient is in each unit. This is necessary to combine the last three records to show just a single line summary for these two lines. What makes this challenging is that I still need to keep a previous time the patient was in unit 3C as separate line. The result set should look like the following:

1234 3C 5/2/09 12:00 5/5/09 13:00
1234 3FE 5/5/09 13:00 5/7/09 18:00
1234 ICU 5/7/09 18:00 5/16/09 5:00
1234 3C 5/16/09 5:00 5/28/09 8:00

Any suggestions would be appreciated.

-Kevin

JarlH (Programmer) · Answer 1 · 2009-08-25 04:01:45

Consider using a cursor.

Or perhaps this will do it:

select patient, unit, min(start_time) as start_time, end_time
from
(select patient, unit, start_time,
         coalesce((select min(t2.start_time)
                   from t as t2
                   where t2.start_time > t.start_time
                   and t2.patient = t.patient
                   and t2.unit <> t.unit),
                  (select max(t2.start_time)
                   from t as t2
                   where t2.patient = t.patient
                   and   t2.unit = t.unit)) as end_time
  from t)
group by patient, unit, end_time
order by patient, unit, start_time

UNTESTED!

JarlH (Programmer) · Answer 2 · 2009-08-25 04:03:30

RE: Tricky grouping question

JarlH (Programmer) 25 Aug 09 04:03

Oops, remove unit from order by...

JarlH (Programmer) · Answer 3 · 2009-08-25 04:16:54

Never mind, this one's even better and Core SQL-99 compliant!

select distinct patient, unit,
       coalesce((select max(t2.end_time)
                 from t as t2
                 where t2.end_time <= t.start_time
                 and t2.patient = t.patient
                 and t2.unit <> t.unit),
                (select min(t2.start_time)
                 from t as t2
                 where t2.patient = t.patient
                 and   t2.unit = t.unit)) as start_time,
       coalesce((select min(t2.start_time)
                 from t as t2
                 where t2.start_time >= t.start_time
                 and t2.patient = t.patient
                 and t2.unit <> t.unit),
                (select max(t2.end_time)
                 from t as t2
                 where t2.patient = t.patient
                 and   t2.unit = t.unit)) as end_time
from t
order by patient, start_time;

Come Join Us!

Posting Guidelines

Tricky grouping question