• Status: Solved
• Priority: Medium
• Security: Public
• Views: 597
• Last Modified:

# Readmission Dates

I work for a hopital and I need to see the date difference between admissions.  My data is below.

PatientID      VisitID      AdmitDateTime      DischargeDateTime
M551955      E2472054      02/27/2012  7:35AM      02/27/2012  1:47PM
M551955      E2473744      02/29/2012  7:17AM      02/29/2012  2:29PM
M551955      E2477588      03/02/2012  7:32AM      03/02/2012  2:00PM
M551955      E2478997      03/05/2012  7:35AM      03/05/2012  3:34PM
M58040      E2444570      01/19/2012  4:01PM      01/25/2012  8:00PM
M58040      E2463583      02/13/2012 10:00PM      02/18/2012  3:04PM
M58040      E2502936      04/06/2012 10:00AM      04/10/2012  9:32PM

I want to show the date difference beteen the 1st and 2nd date and the 2nd and 3rd, etc.  How can I calculate the difference on the previous record? I need the calculation to be diff of AdmitDateTime and DischargeDateTime.

Some Patients have 27 records, some only have 2.
0
Scotto123
Asked:
• 4
• 3
1 Solution

Commented:
try this query

SELECT Patient.PatientID, Patient.VisitID, Patient.AdmitDateTime, (select max([AdmitDateTime]) from Patient P where P.PatientID=Patient.PatientID and P.AdmitDateTime<Patient.AdmitDateTime) AS PrevAdmissionDate, DateDiff("d",[PrevAdmissionDate],[AdmitDateTime]) AS AdmissonDiff, Patient.DischargeDateTime,(select max([DischargeDateTime]) from Patient P where P.PatientID=Patient.PatientID and P.DischargeDateTime<Patient.DischargeDateTime) AS PrevDischargeDate, DateDiff("d",[PrevDischargeDate],[DischargeDateTime]) AS DischargeDiff
FROM Patient
ORDER BY Patient.PatientID, Patient.AdmitDateTime;
0

Author Commented:
Works great.  Thanks Cap!!
0

Author Commented:
Cap,
I found out the query isn't sorting the records correctly.  Can you make it so they are sorted by the Admit Date
As you can see there are negative values in Diff because the data isn't sorted when it calculates the diff.

PatientID      VisitID      AdmitDateTime      DischargeDate    PrevDisDate      DischargeDiff
M427799      E2265109      09/26/2011 11:34AM      09/29/2011  1:17PM      03/22/2012 12:53PM      -175
M427799      E2360824      09/29/2011  1:30PM      10/08/2011 11:40AM      09/29/2011  1:17PM      9
M427799      E2477999      03/21/2012  9:26AM      03/22/2012 12:53PM

Hope you can help even though I approved.
0

Commented:
how can that be?

upload a a db with the table..
0

Author Commented:
Attached - Query1 is your query.  Thanks Cap.  Let me know if you want a new question.
VisitAnalysisExample.accdb
0

Commented:
your AdmitDateTime   and   DischargeDate  fields are set as Text Data Type
change them to Date/Time data type
0

Author Commented:
Got it.  All set now.  Thanks a lot for your attention to this!!
0

## Featured Post

• 4
• 3
Tackle projects and never again get stuck behind a technical roadblock.