# 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

• 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
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
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 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
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
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 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