• 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:
Scotto123
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)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
 
Scotto123Author Commented:
Works great.  Thanks Cap!!
0
 
Scotto123Author 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
how can that be?

upload a a db with the table..
0
 
Scotto123Author Commented:
Attached - Query1 is your query.  Thanks Cap.  Let me know if you want a new question.
VisitAnalysisExample.accdb
0
 
Rey Obrero (Capricorn1)Commented:
your AdmitDateTime   and   DischargeDate  fields are set as Text Data Type
change them to Date/Time data type
0
 
Scotto123Author Commented:
Got it.  All set now.  Thanks a lot for your attention to this!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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