Friday, September 07, 2012 4:26 AM
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..
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
So what i did is the following
Friday, September 07, 2012 7:11 AMDavid.. All the records mentioned above are for the same patient. So how would you different the day that the patient gave birth.
Saturday, September 08, 2012 3:56 AM
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).
Show me all records of birth and the length of stay 'from Birth to discharge' and the average LOS in days
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)
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
Saturday, September 08, 2012 4:47 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;
- Marked As Answer by KIWI DAVE Saturday, September 08, 2012 5:30 AM
Saturday, September 08, 2012 5:23 AM
I will be looking into this at work
Thanks for the input.