Readmission Dates

Posted on 2012-09-10
Last Modified: 2012-09-11
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.
Question by:Scotto123
    LVL 119

    Accepted Solution

    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;

    Author Closing Comment

    Works great.  Thanks Cap!!

    Author Comment

    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.
    LVL 119

    Expert Comment

    by:Rey Obrero
    how can that be?

    upload a a db with the table..

    Author Comment

    Attached - Query1 is your query.  Thanks Cap.  Let me know if you want a new question.
    LVL 119

    Expert Comment

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

    Author Comment

    Got it.  All set now.  Thanks a lot for your attention to this!!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now