none
Calculate Length of stay (Comments on a better way to achieve this)

    Question

  • Hi i had the following problem, which i have solved but it took me several steps to complete,

    I am wondering if i could have achieved it a better way..

    Problem statement:

    Hospital data

    Show me the Length of stay for all mothers who have given birth

    Seems simple enough but there is no flag, and mothers move to different wards for the same length of stay.

    I have attached the following to show this

    PK = PrimaryKey, PID = PatientID , SD = StartDateTime, ED = EndDateTime

    In the above example PK 3501978 is the Last record for this Patient, ED is the day the patient left hospital.

    PK 3501977 is the record before and the ED is the date the mother gave birth

    I needed to calculate the dateDiff between these two records as the Length of stay after Birth

    Then run this for each group of PatientID

    If anyone would like to post their ideas, this would help me to see if could become more efficient or maybe my way is ok

    thanks

    So what i did is the following


    David

    Friday, September 07, 2012 4:26 AM

Answers

  • If all you want is the difference in days for the ED column for each patient (note the following gives the birth date and discharge date for each patient, if you don't want that, just remove those columns from the final select

    ;With cte As
    (Select PID, ED,
       Row_Number() Over(Partition By PID Order By ED Desc) As rn
    From <your table name>),
    PatientData As
    (Select PID, Max(Case When rn = 2 Then ED End) As BirthDate,
       Max(Case When rn = 1 Then ED End) As DischargeDate
    From cte
    Where rn In (1,2)
    Group By PID
    Having Max(rn) = 2)
    Select PID, BirthDate, DischargeDate, 
       DateDiff(day, BirthDate, DischargeDate) As LengthOfStay
    From PatientData;

    And the average length of stay after birth is

    ;With cte As
    (Select PID, ED,
       Row_Number() Over(Partition By PID Order By ED Desc) As rn
    From <your table name>),
    PatientData As
    (Select PID, Max(Case When rn = 2 Then ED End) As BirthDate,
       Max(Case When rn = 1 Then ED End) As DischargeDate
    From cte
    Where rn In (1,2)
    Group By PID
    Having Max(rn) = 2)
    Select Avg(DateDiff(day, BirthDate, DischargeDate)) As AverageLengthOfStay
    From PatientData;

    Tom

    • Marked as answer by KIWI DAVE Saturday, September 08, 2012 5:30 AM
    Saturday, September 08, 2012 4:47 AM

All replies

  • David.. All the records mentioned above are for the same patient. So how would you different the day that the patient gave birth.

    Murali Krishnan

    Friday, September 07, 2012 7:11 AM
  • Hi Murali

    Each of the records are movements to different beds, and or movement to different bed in different ward the last record would be a movement to say Bed04(and go home) the previous record would be either DeliveryBed or BirthingSuite..so the last record regardless if the mother stayed in the Birthing or Delivery Bed until discharge is the datetime of discharge from the hospital, so the previous record is considered to be the datetime of Birth (a bit of fuzzy logic).

    Brief was;

    Show me all records of birth and the length of stay 'from Birth to discharge' and the average LOS in days

    My method

    I created a table called stg_Maternity

    Bought all the maternity records into this staging table.

    and created 3 columns

    GoHomeFlag, PreviousRecordFlag, PreviousRecordDateTime (these are for update query later)

    First

    Create tempTables

    So i had to group  PatientID Max(ED date) to get a set of records that was the last record (or the go home record), ready for the new column called 'GoHomeFlag" and updated the recordset where PatID=PatID and MaxEDDate = EDdate,

    Now i had a full set of records with some records Flagged GoHome,

    Next i created another set of Records PatientID Max(ED Date) group by PatID = PatID and MaxEDDate = EDdate where GoHomeFlag is NULL

    Now i have to tempTables one holds Records for GoHome, the other holds Records for Previous Record..

    I created a new table on the fly and called it dbo.MaterintyLOS (Maternity length of stay) rather than use the stg table but only records that had the GoHomeFlag = 1

    I then updated my subset with the column PreviousRecordFlag and PreviousRecordDateTime where PatID=PatID etc...

    I now have a recordset of say 50000 goHome records with the ability to calculate a length of stay from mum having the baby to discharge, dateDiff (EDdate - PreviousRecordDateTime) also i can say what is the average length of stay for mothers after giving birth, we use this to work out a cost per patient.

    It took me 3 hours from the brief to get my head around the logic and to supplying the output,

    the reason for the post was to see if i could have done something like this better or faster, maybe instead of using temTables and update queries someone could see a better and faster method.

    Let me know what you think


    David

    Saturday, September 08, 2012 3:56 AM
  • If all you want is the difference in days for the ED column for each patient (note the following gives the birth date and discharge date for each patient, if you don't want that, just remove those columns from the final select

    ;With cte As
    (Select PID, ED,
       Row_Number() Over(Partition By PID Order By ED Desc) As rn
    From <your table name>),
    PatientData As
    (Select PID, Max(Case When rn = 2 Then ED End) As BirthDate,
       Max(Case When rn = 1 Then ED End) As DischargeDate
    From cte
    Where rn In (1,2)
    Group By PID
    Having Max(rn) = 2)
    Select PID, BirthDate, DischargeDate, 
       DateDiff(day, BirthDate, DischargeDate) As LengthOfStay
    From PatientData;

    And the average length of stay after birth is

    ;With cte As
    (Select PID, ED,
       Row_Number() Over(Partition By PID Order By ED Desc) As rn
    From <your table name>),
    PatientData As
    (Select PID, Max(Case When rn = 2 Then ED End) As BirthDate,
       Max(Case When rn = 1 Then ED End) As DischargeDate
    From cte
    Where rn In (1,2)
    Group By PID
    Having Max(rn) = 2)
    Select Avg(DateDiff(day, BirthDate, DischargeDate)) As AverageLengthOfStay
    From PatientData;

    Tom

    • Marked as answer by KIWI DAVE Saturday, September 08, 2012 5:30 AM
    Saturday, September 08, 2012 4:47 AM
  • Thanks Tom,

    I will be looking into this at work

    Thanks for the input.

    :)


    David

    Saturday, September 08, 2012 5:23 AM