Using value in previous and next records to get admit & discharge date

[GunVi]

Hi,

I have a patient location table with patient ID, location, start date, end date and service. This data is generated by the admission and discharge system.

I have two issues that need to be solved. They are:
1) Make sure that the data is cleaned and the correct records are considered for calculating length of stay.

2) Calculate start and end dates for unit admissions based on the fact that if the patient is readmitted to the same unit within 0.5 days(12 hours) it is not a new admission (i.e. has the start date of the first admission but the end date of the readmission).

The patient location table (TblPtLocations) has the following fields and data.
PatientID Location StartDate EndDate SERVICE
1034 Location2 10/30/2009 11:44:00 PM 11/03/2009 1:22:00 AM Medicine
1034 Location2 10/31/2009 12:16:00 AM 10/30/2009 11:44:00 PM Surgery
1034 Location2 10/31/2009 12:16:00 AM 12/29/2009 5:26:00 AM Surgery
1034 Location3 11/03/2009 1:22:00 AM 11/05/2009 12:36:00 AM Medicine
1034 Location2 11/05/2009 12:36:00 AM 11/08/2009 4:41:00 PM Medicine
1034 Location1 11/08/2009 4:41:00 PM 11/08/2009 5:51:00 PM Medicine
1034 Location1 11/08/2009 5:51:00 PM 11/09/2009 7:03:00 PM Medicine
1034 Location1 12/24/2009 3:28:00 AM 11/09/2009 7:04:00 PM Medicine
1034 Location1 12/29/2009 4:41:00 AM 12/29/2009 4:42:00 AM Medicine
1034 Location2 12/29/2009 4:42:00 AM 12/29/2009 4:43:00 AM Surgery
1034 Location1 12/29/2009 5:26:00 AM 11/09/2009 7:04:00 PM Surgery
1034 Location2 12/29/2009 10:40:00 AM 12/31/2009 11:15:00 AM Surgery

The cleaned data table should be
PatientID Location StartDate EndDate SERVICE
1034 Location2 10/30/2009 11:44:00 PM 11/03/2009 1:22:00 AM Medicine
1034 Location3 11/03/2009 1:22:00 AM 11/05/2009 12:36:00 AM Medicine
1034 Location2 11/05/2009 12:36:00 AM 11/08/2009 4:41:00 PM Medicine
1034 Location1 11/08/2009 4:41:00 PM 11/08/2009 5:51:00 PM Medicine
1034 Location1 11/08/2009 5:51:00 PM 11/09/2009 7:03:00 PM Medicine
1034 Location1 12/29/2009 4:41:00 AM 12/29/2009 4:42:00 AM Medicine
1034 Location2 12/29/2009 4:42:00 AM 12/29/2009 4:43:00 AM Surgery
1034 Location2 12/29/2009 10:40:00 AM 12/31/2009 11:15:00 AM Surgery

The unit admission table should be the following
PatientID Location StartDate EndDate SERVICE
1034 Location2 10/30/2009 11:44:00 PM 11/03/2009 1:22:00 AM Medicine
1034 Location3 11/03/2009 1:22:00 AM 11/05/2009 12:36:00 AM Medicine
1034 Location2 11/05/2009 12:36:00 AM 11/08/2009 4:41:00 PM Medicine
1034 Location1 11/08/2009 4:41:00 PM 11/09/2009 7:03:00 PM Medicine
1034 Location1 12/29/2009 4:41:00 AM 12/29/2009 4:42:00 AM Medicine
1034 Location2 12/29/2009 4:42:00 AM 12/31/2009 11:15:00 AM Surgery

I am not sure of how to accomplish this. Any help would be appreciated.

Thanks!

 

[MajP]

Is this a typo? Trying to figure out the logic. The surgery ends a 2 months later and does not show in your clean data.

1034 Location2 10/31/2009 12:16:00 AM 12/29/2009 5:26:00 AM Surgery

 

[MajP]

I am having a hard time understanding your data. You have end dates that are prior to the start date (second record). The third record is a half day after the end of the second record but the same start as record 2. So what do you mean by readmit of .5days. How do you calculate elapsed time between records. Makes no sense.

1034 Location2 10/30/2009 11:44:00 PM 11/03/2009 1:22:00 AM Medicine
1034 Location2 10/31/2009 12:16:00 AM 10/30/2009 11:44:00 PM Surgery
1034 Location2 10/31/2009 12:16:00 AM 12/29/2009 5:26:00 AM Surgery

 

[MajP]

I tried to look at this one more time, but got more confued. It appears that the .5 days is based only on start time. So the following three records are a part of a group. I will call it a sequence.

1 1034 Location2 10/30/2009 11:44:00 PM 11/03/2009 1:22:00 AM Medicine
2 1034 Location2 10/31/2009 12:16:00 AM 10/30/2009 11:44:00 PM Surgery
3 1034 Location2 10/31/2009 12:16:00 AM 12/29/2009 5:26:00 AM Surgery

Each start date is less than .5 apart. So keep the first record only (the admission), but what do you choose as the end date? According to

has the start date of the first admission but the end date of the readmission

I would think the record would look like the following with the greatest readmission end date and the admission start date.
1034 Location2 10/30/2009 11:44:00 PM 12/29/2009 5:26:00 AM Medicine
but you show
1034 Location2 10/30/2009 11:44:00 PM 11/03/2009 1:22:00 AM Medicine

Also you have this.
1 1034 Location1 11/08/2009 4:41:00 PM 11/08/2009 5:51:00 PM Medicine
2 1034 Location1 11/08/2009 5:51:00 PM 11/09/2009 7:03:00 PM Medicine
3 1034 Location1 12/24/2009 3:28:00 AM 11/09/2009 7:04:00 PM Medicine
4 1034 Location1 12/29/2009 4:41:00 AM 12/29/2009 4:42:00 AM Medicine

Record 1 and 2 are a sequence, and 3 and 4 are a sequence
This makes sense to me in the clean data:
1034 Location1 11/08/2009 4:41:00 PM 11/09/2009 7:03:00 PM Medicine
(admission start date, and the readmission end date)

But this does not make sense:
1034 Location1 12/29/2009 4:41:00 AM 12/29/2009 4:42:00 AM Medicine

I would thing this should be the clean answer
1034 Location1 12/24/2009 3:28:00 AM 12/29/2009 4:04200 AM Medicine
Record 3 is the start of a new sequence.

I can provide an answer, if you can explain the logic. I just cannot see any rull to apply.

 

[MajP]

should read
I would think this should be the clean answer
1034 Location1 12/24/2009 3:28:00 AM 12/29/2009 4:42:00 AM Medicine