CODEWITHSUNDEEP
sql
user2120786
user2120786

Reputation: 3

join status history

I have a table listing patient days for encounters. Below a sample for one encounter. Every day the patient is in the hospital one record is created at midnight untill discharge.

Enc_iD  Day_Id      ServiceDtTm        AdmitDate
2616350 34707672    2/21/2013 23:59    21/FEB/13 12:19:00
2616350 34733898    2/22/2013 23:59    21/FEB/13 12:19:00
2616350 34748155    2/23/2013 23:59    21/FEB/13 12:19:00
2616350 34760403    2/24/2013 23:59    21/FEB/13 12:19:00
2616350 34784357    2/25/2013 23:59    21/FEB/13 12:19:00
2616350 34808228    2/26/2013 23:59    21/FEB/13 12:19:00
2616350 34814512    2/27/2013 10:10    21/FEB/13 12:19:00

In a separate table a status for every encounter is maintained irregulary by a user.

Enc_iD  TransDtTm       Status
2616350 2/21/2013 12:20 
2616350 2/21/2013 13:29 1
2616350 2/22/2013 7:28  3
2616350 2/25/2013 13:44 2
2616350 2/27/2013 10:10 2

I want to create a resultset with SQL as below. So for every day in the top table the status that was the most recent at that ServiceDtTm.

Enc_iD  Day_Id      DtTime             AdmitDate            Status
2616350 34707672    2/21/2013 23:59    21/FEB/13 12:19:00   1
2616350 34733898    2/22/2013 23:59    21/FEB/13 12:19:00   3
2616350 34748155    2/23/2013 23:59    21/FEB/13 12:19:00   3
2616350 34760403    2/24/2013 23:59    21/FEB/13 12:19:00   3
2616350 34784357    2/25/2013 23:59    21/FEB/13 12:19:00   2
2616350 34808228    2/26/2013 23:59    21/FEB/13 12:19:00   2
2616350 34814512    2/27/2013 10:10    21/FEB/13 12:19:00   2

Any help is appreciated. Can't figure this out SQL. Only in excel by using vlookup with approximate match.

Robbert

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think the simplest solution is a correlated subquery. Here is the syntax in SQL Server:

select pd.*,
       (select top  1  status
        from status s 
        where s.enc_id = pd.enc_id and s.transdttm <= pd.servicedttm
        order by s.transdttm
       ) as MostRecentStatus
from patientdays pd

For most other databases, it would look like:

select pd.*,
       (select status
        from status s 
        where s.enc_id = pd.enc_id and s.transdttm <= pd.servicedttm
        order by s.transdttm
        limit 1
       ) as MostRecentStatus
from patientdays pd

Upvotes: 1

Related Questions