×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tricky grouping question

Tricky grouping question

Tricky grouping question

(OP)
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
 

RE: Tricky grouping question

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!

RE: Tricky grouping question

Oops, remove unit from order by...

RE: Tricky grouping question

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;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close