Tricky grouping question

[kdbenson]

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]

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]

Oops, remove unit from order by...

 

[JarlH]

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;