November 7, 2007 at 2:21 pm
Hi Guys
I have not been able to figure out how to solve this problem.
I have got a master table of patients which stores patientsinformation for different clinical units such as rest homes, hospitals etc.
I need to calculate the average length of stay of patients under each unit for each quarter
Assumption: a patient can move from one unit to other but we will calculate the Length of stay for that patient each time the patient has been cured.
Here is the sample data
Patient ID StartDate EndDate ClinicUnit
1A 1/1/04 12/1/04 Hospital
2A 12/5/05 15/9/07 Rest Home
3A 3/5/06 12/5/06 Rest Home
..
.
.
Now I have got a dates table where I have got each possible date from 1/1/2004 to 31/12/2013
Here is the sample data for the dates table
Date Qtr
1/1/2004 Q1/2004
1/3/2003 Q1/2004
21/6/2007 Q2/2007
.
.
.
NOw Length of stay is calculated as follows
FOr ex for the first Qtr,
where end date between ('2004-01-01 and '2004-03-31)
LOS = 'datediff(dd,start date,end date)
I need the output table as follows
Qrtr PatientID AvgLOS
Q1/2004 1A 24
Q1/205 1B 123
Q2/2007 4R 365
How do I do that guys??
Thnaks
November 7, 2007 at 2:44 pm
Something similar to thisSELECTm.PatientID,
m.ClinicUnit,
d.Qtr,
COUNT(*) AS Days
FROMMaster AS m
CROSS APPLY(
SELECTd.Date,
d.Qtr
FROMDates AS d
WHEREd.Date BETWEEN m.StartDate AND m.EndDate
) AS d
GROUP BYm.PatientID,
m.ClinicUnit,
d.Qtr
N 56°04'39.16"
E 12°55'05.25"
November 7, 2007 at 2:46 pm
I think I misunderstand your requirements. You state you need to calculate the avergae length of stay for each quarter per patient. But you are doing it by year. Do you mean that if a persons stays anywhere within a quarter that you figure the days and divided by the number of times? So some folks could stay 92 day average because they stayed 1 place for the full 92 days (average for a long quarter), but a person who stayed 3 places 4 times with a total of 92 days would have an average of 23 days? Is this right? And what is the goal here so I understand why?
November 7, 2007 at 2:54 pm
SELECTe.PatientID,
e.Qtr,
AVG(e.Days) AS avgLos
FROM(
SELECTm.PatientID,
m.ClinicUnit,
d.Qtr,
COUNT(*) AS Days
FROMMaster AS m
CROSS APPLY(
SELECTx.Date,
x.Qtr
FROMDates AS x
WHEREx.Date BETWEEN m.StartDate AND m.EndDate
) AS d
GROUP BYm.PatientID,
m.ClinicUnit,
d.Qtr
) AS e
GROUP BYe.PatientID,
e.Qtr
N 56°04'39.16"
E 12°55'05.25"
November 7, 2007 at 4:11 pm
Hi
Ill explain the situation again
Its really simple for ex a patient 'A' was in rest home from '1/1/2004' to 12/2/2004'
and then the same patient 'A' was then moved to hospital from 12/03/2005 till 12/06/2007'
then he will be counted once for rest home and once for hospital.
And I have to calculate the no of days of stay by substracting start date from the end date.
Lets say I need data for quarter 1
My condition will be as follows
If end date between 1/1/2004 and 31/3/2004
LOS = end date - start date
Does it make sense now??
Thanks
November 7, 2007 at 6:10 pm
Nuts (11/7/2007)
HiIll explain the situation again
Its really simple for ex a patient 'A' was in rest home from '1/1/2004' to 12/2/2004'
and then the same patient 'A' was then moved to hospital from 12/03/2005 till 12/06/2007'
then he will be counted once for rest home and once for hospital.
Similiar to what was previously posted. You didn't tell us what the master table was called so I called it PatientStay:
SELECT d.Qtr, PS.PatientID, PS.ClinicUnit, COUNT(*) LOS
FROM PatientStay PS
CROSS APPLY
(SELECT Date, Qtr
FROM Dates
WHERE Date BETWEEN StartDate AND EndDate) d
GROUP BY d.Qtr, PS.PatientID, PS.ClinicUnit
The reason the COUNT(*) works is you are using CROSS APPLY to return every applicable day between the start date and end date and the GROUP BY segments them into the proper quarters. This effectively does the same thing as the DATEDIFF. I've included the ClinicUnit so you can see the break between different clinical units.
K. Brian Kelley
@kbriankelley
November 7, 2007 at 6:38 pm
Hi
Thanks for your help
I ran the query but the figures are really really low!!
November 7, 2007 at 6:55 pm
Nuts (11/7/2007)
I ran the query but the figures are really really low!!
Remember, that the highest you're going to get for any quarter is ~ 90 days (3 months x 30 days = 90 days). You can run a reconciliation query by DATEDIFFing all the start & end dates in the original table and using SUM() of those values to get a total. Then SUM() the total from the query. They should be equal.
Also, do you have any cases where the EndDate is NULL (because the patient is still in the facility)? If so, use COALESCE(EndDate, GETDATE()) instead of just EndDate in that subquery.
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply