Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with visit summary of patients visiting the hospital. This table contains the VisitID, PatientID, DateAdmitted, DateDischarged. I need to identify visits which were readmissions (i.e. visits which happened within a gap of 30 days from the previous visit). I need to add two columns flagging the visit as a readmission using the DateAdmitted and DateDischarged as Readmit_flag (a visit from a patient who was admitted in the hospital less than 30 days ago) and PrevAdmit_flag (an admission which lead to a readmission).
Dummy Data:
VisitID | PatientID | DateAdmitted | DateDischarge |
AA1 | PA1 | 5/26/2016 | 6/10/2016 |
AA2 | PA2 | 6/7/2016 | 6/13/2016 |
AA3 | PA3 | 5/30/2016 | 6/5/2016 |
AA4 | PA1 | 7/1/2016 | 7/5/2016 |
AA5 | PA2 | 7/1/2016 | 7/7/2016 |
AA6 | PA3 | 7/3/2016 | 7/7/2016 |
AA7 | PA4 | 8/3/2016 | 8/6/2016 |
AA8 | PA5 | 8/4/2016 | 8/9/2016 |
AA9 | PA4 | 9/4/2016 | 9/8/2016 |
Output:
VisitID | PatientID | DateAdmitted | DateDischarge | Readmit_Flag | PrevAdmit_flag |
AA1 | PA1 | 5/26/2016 | 6/10/2016 | 0 | 1 |
AA2 | PA2 | 6/7/2016 | 6/13/2016 | 0 | 1 |
AA3 | PA3 | 5/30/2016 | 6/5/2016 | 0 | 1 |
AA4 | PA1 | 7/1/2016 | 7/5/2016 | 1 | 0 |
AA5 | PA2 | 7/1/2016 | 7/7/2016 | 1 | 0 |
AA6 | PA3 | 7/3/2016 | 7/7/2016 | 1 | 0 |
AA7 | PA4 | 8/3/2016 | 8/6/2016 | 0 | 0 |
AA8 | PA5 | 8/4/2016 | 8/9/2016 | 0 | 0 |
AA9 | PA4 | 9/7/2016 | 9/10/2016 | 0 | 0 |
PA1, PA2, PA3 got readmitted within 30 days of their previous discharge, so their readmit_flag is 1 and the corresponding previous visit is flagged with PrevAdmit_flag as 1. PA4 was admitted after more than 30 days so flag is 0 for both readmit and prevadmit.
Please help me achieve this on the Load Editor. Thank you.
If it is starting from 'now', I would create a temp table that has the max DateDischarged for each patient ID.
Then left join that max(DateDischarged) back to the main table and then do a DateDiff then use Interval(AdmissionDate - max(DateDischarged)) to see if the patient is flagged as discharged within the last 30days.
Thats my sketched out version for a process to use for future admissions. Though if you are looking through past data, this method won't find the correct Discharge Date to correspond to the 'last visit' if that patient has been discharged again.
My thought for that use case would be to load the records, group by patientId and order by discharge date, then potentially use a Previous() function to check each record against the record preceding it to compare the discharge date against the admission date?
Does any of that make sense?