January 28, 2011 at 8:22 am
We have hospital census data, ordered by the Patient and PatientEpisode number. Within each episode, each record is ordered with the Counter column. What I'm trying to do is to calculate the amount of time that a person meets a set of criteria (CriteriaYes) while in the hospital. This should be calculated from the Census_DNR where the first record in a patient's episode has criteriayes = 1 and the first subsequent record with a criteriayes = 0.
Here's some sample data:
CREATE TABLE #Census (
Patientint
, PatientEpisodeint
, CensusDNRdatetime
, CriteriaYesint
, Counterint
)
INSERT INTO #Census
VALUES (12554,3,'2009-12-01 21:33:00.000',1,1)
INSERT INTO #Census
VALUES (12554,3,'2009-12-02 09:54:58.017',1,2)
INSERT INTO #Census
VALUES (12554,3,'2009-12-02 16:30:33.003',0,3)
INSERT INTO #Census
VALUES (19973,2,'2008-07-22 12:21:49.447',0,1)
INSERT INTO #Census
VALUES (19973,2,'2008-07-23 10:15:04.583',0,2)
INSERT INTO #Census
VALUES (19973,2,'2008-07-28 02:33:01.937',0,3)
INSERT INTO #Census
VALUES (19973,2,'2008-07-29 04:23:45.107',0,4)
INSERT INTO #Census
VALUES (19973,2,'2008-07-29 21:11:42.947',1,5)
INSERT INTO #Census
VALUES (19973,2,'2008-07-31 01:32:04.623',1,6)
INSERT INTO #Census
VALUES (19973,2,'2008-08-12 12:14:11.903',1,7)
INSERT INTO #Census
VALUES (19973,2,'2008-08-14 04:53:38.130',0,8)
INSERT INTO #Census
VALUES (19973,2,'2008-08-18 10:32:36.040',0,9)
INSERT INTO #Census
VALUES (194221,1,'2008-09-18 11:59:47.463',1,1)
INSERT INTO #Census
VALUES (194221,1,'2008-09-18 15:12:32.707',1,2)
INSERT INTO #Census
VALUES (194221,1,'2008-10-04 12:24:41.377',0,3)
INSERT INTO #Census
VALUES (194221,1,'2008-10-14 04:06:10.080',1,4)
INSERT INTO #Census
VALUES (194221,1,'2008-10-22 11:14:27.417',0,5)
The dataset looks like:
1255432009-12-01 21:33:00.00011
1255432009-12-02 09:54:58.01712
1255432009-12-02 16:30:33.00303
1997322008-07-22 12:21:49.44701
1997322008-07-23 10:15:04.58302
1997322008-07-28 02:33:01.93703
1997322008-07-29 04:23:45.10704
1997322008-07-29 21:11:42.94715
1997322008-07-31 01:32:04.62316
1997322008-08-12 12:14:11.90317
1997322008-08-14 04:53:38.13008
1997322008-08-18 10:32:36.04009
19422112008-09-18 11:59:47.46311
19422112008-09-18 15:12:32.70712
19422112008-10-04 12:24:41.37703
19422112008-10-14 04:06:10.08014
19422112008-10-22 11:14:27.41705
So, based on each episode I would expect to find:
12554 3 ElapsedTime = (2009-12-02 16:30:33.003 - 2009-12-01 21:33:00.000 ) , that is, Counter row 3 - Counter row 1
19973 2 ElapsedTime = (2008-08-14 04:53:38.130 - 2008-07-29 21:11:42.947 ) , that is, Counter row 8 - Counter row 5
194221 1 ElapsedTime = (2008-10-04 12:24:41.377 - 2008-09-18 11:59:47.463 ) + , that is, Counter row 3 - Counter row 1
194221 1 ElapsedTime = (2008-10-22 11:14:27.417 - 2008-10-14 04:06:10.080 ) , that is, Counter row 5 - Counter row 4
Any help greatly appreciated,
Steve Lord
January 28, 2011 at 8:29 am
The usual way to do this kind of thing is:
select Col1, Col2, DateTimeCol,
(select min(DateTimeCol)
from MyTable as MyTable2
where MyTable2.Col1 = MyTable1.Col1
and MyTable2.Col2 = MyTable1.Col1
and MyTable2.DateTimeCol > MyTable1.DatetimeCol
and MyTable2.YesNoCol = 0) as EndTime
from MyTable as MyTable1
where YesNoCol = 1;
Depending on the version of SQL Server, you can turn that into a Cross or Outer Apply and potentially make it easier to maintain, and possibly faster as well.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2011 at 9:25 am
Thanks ever so much! That definitely helped.
The only thing left was for me to group on col1, col2, and endtime to eliminate the "in-between" comparisons since I needed only the first time criteria was met compared to when criteria no longer met:
SELECT Col1, Col2, min(DateTimeCol), EndTime
FROM (
select Col1, Col2, DateTimeCol,
(select min(DateTimeCol)
from MyTable as MyTable2
where MyTable2.Col1 = MyTable1.Col1
and MyTable2.Col2 = MyTable1.Col2
and MyTable2.DateTimeCol > MyTable1.DatetimeCol
and MyTable2.YesNoCol = 0) as EndTime
from MyTable as MyTable1
where YesNoCol = 1
) Q
GROUP BY Col1, Col2, EndTime
Thanks again,
Steve
January 28, 2011 at 11:13 am
If you are using SQL Server 2005 or 2008, this method using ROW_NUMBER() might work for you:
;WITH cteSEQ AS (
SELECT
Patient,
PatientEpisode,
CensusDNR,
CriteriaYes,
Counter,
Counter - ROW_NUMBER() OVER (
PARTITION BY Patient, PatientEpisode, CriteriaYes
ORDER BY Counter
) AS grp
FROM #Census
), cteGROUP AS (
SELECT
Patient,
PatientEpisode,
MIN(CensusDNR) AS CensusDNR,
CriteriaYes,
ROW_NUMBER() OVER (
PARTITION BY Patient, PatientEpisode
ORDER BY MIN(Counter)
) AS rn
FROM cteSEQ
GROUP BY Patient, PatientEpisode, CriteriaYes, grp
)
SELECT
G1.Patient,
G1.PatientEpisode,
SUM(CAST(G2.CensusDNR - G1.CensusDNR AS float)) AS ElapsedTimeInDays
FROM cteGROUP G1
JOIN cteGROUP G2 ON (
G1.Patient = G2.Patient
AND G1.PatientEpisode = G2.PatientEpisode
AND G1.rn = G2.rn - 1
)
WHERE (G1.CriteriaYes = 1 AND G2.CriteriaYes = 0)
GROUP BY G1.Patient, G1.PatientEpisode
ORDER BY G1.Patient, G1.PatientEpisode
This query returns the following results using your test data:
Patient PatientEpisode ElapsedTimeInDays
----------- -------------- ----------------------
12554 3 0.789965316358025
19973 2 15.3207775848765
194221 1 24.3147135416667
January 28, 2011 at 12:04 pm
Perfect! I'm on SQL Server 2005 so that works great.
Thanks very much
January 28, 2011 at 12:24 pm
A couple of points to check:
If there is a possibility of gaps in the integer values of the Counter column for a given patient episode, then the query will need amending slightly, since the the "Counter - ROW_NUMBER()" expression in the first CTE will not work as intended if the Counter column has gaps.
Also, are you interested in any time intervals that are still ongoing, i.e. where the most recent row for a given patient episode has a CriteriaYes column value of 1? If so, the JOIN in the final SELECT statement will need changing to a LEFT JOIN alongside some other changes, such that the current date/time is substituted for the missing end date/time value.
January 31, 2011 at 7:30 am
The possibility of a missing end value is why I use an inline subquery instead of the CTE method.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply