SQLTeam.com | Weblogs | Forums

How to roll up multiple dates with continuous episode on same day to a single row

The logic that allows episodes to be condensed into a single continuous care episode is a discharge code of 22 followed by an admission code of 4 on the same day.
Note:epn field from the source table represents each episode, but episode_c_care_key for the output is a derived field which can be a row number.

drop table #source
CREATE TABLE #source(patidid varchar(20),epn int,preadmitdate datetime,adminttime varchar(10),
admitcode varchar(10),datedischarge datetime,disctime varchar(10),disccode varchar(10))
INSERT INTO #source VALUES
(1849,1,'4/23/2020','7:29',1,'7/31/2020','9:03',22)
,(1849,2,'7/31/2020','11:00',4,'7/31/2020','12:09',22)
,(1849,3,'7/31/2020','13:10',4,'8/24/2020','10:36',10)
,(1849,4,'8/26/2020','12:25',2,null,null,null)
,(1850,1,'4/23/2020','7:33',1,'6/29/2020','7:30',22)
,(1850,2,'6/29/2020','9:35',4,'7/8/2020','10:51',7)
,(1850,3,'7/10/2020','11:51',3,'7/29/2020','9:12',7)
,(1850,4,'7/31/2020','11:00',2,'8/6/2020','10:24',22)
,(1850,5,'8/6/2020','12:26',4,null,null,null)
,(1851,1,'4/23/2020','7:35',1,'6/24/2020','13:45',22)
,(1851,2,'6/24/2020','15:06',4,'9/24/2020','15:00',2)
,(1851,3,'12/4/2020','8:59',0,null,null,null)
,(1852,1,'4/23/2020','7:37',1,'7/6/2020','11:15',20)
,(1852,2,'7/8/2020','10:56',0,'7/10/2020','11:46',2)
,(1852,3,'7/10/2020','11:47',2,'7/28/2020','13:16',22)
,(1852,4,'7/28/2020','15:17',4,'8/4/2020','11:37',22)
,(1852,5,'8/4/2020','13:40',4,'11/18/2020','15:43',2)
,(1852,6,'12/2/2020','15:23',2,null,null,null)
,(1853,1,'4/23/2020','7:40',1,'7/1/2020','8:30',22)
,(1853,2,'7/1/2020','14:57',4,'12/4/2020','12:55',7)
,(1854,1,'4/23/2020','7:44',1,'7/31/2020','13:07',20)
,(1854,2,'8/3/2020','16:30',0,'8/5/2020','9:32',2)
,(1854,3,'8/5/2020','10:34',2,'8/24/2020','8:15',22)
,(1854,4,'8/24/2020','10:33',4,'12/4/2020','7:30',22)
,(1854,5,'12/4/2020','9:13',4,null,null,null)

WITH Gaps
AS
(
	SELECT S.patidid
		,X.PIn, X.POut
		,CASE
			WHEN LAG(S.datedischarge) OVER (PARTITION BY S.patidid ORDER BY X.PIn)
					= S.preadmitdate
				AND LAG(S.disccode) OVER (PARTITION BY S.patidid ORDER BY X.PIn)
					= 22
				AND S.admitcode = 4
			THEN 0
			ELSE 1
		END AS Gap
	FROM #source S
		CROSS APPLY
		(
			VALUES
			(
				S.preadmitdate + CAST(S.adminttime AS datetime)
				,S.datedischarge + CAST(S.disctime AS datetime)
			)
		) X (PIn, POut)
)
,Grps
AS
(
	SELECT patidid
		,PIn, POut
		,SUM(Gap) OVER (PARTITION BY patidid ORDER BY PIn) AS Grp
	FROM Gaps
)
,Results
AS
(
	SELECT patidid
		,MIN(PIn) AS PIn
		,MAX(POut) AS POut
	FROM Grps
	GROUP BY patidid, Grp
)
SELECT R.patidid
	,ROW_NUMBER() OVER (PARTITION BY R.patidid ORDER BY R.PIn) AS episode_c_care_key
	,DATEADD(day, 0, DATEDIFF(day, 0, R.PIn)) AS preadmitdate
	,CAST(CAST(R.PIn AS time) AS varchar(5))  AS adminttime
	,DATEADD(day, 0, DATEDIFF(day, 0, R.POut)) AS datedischarge
	,CAST(CAST(R.POut AS time) AS varchar(5))  AS disctime
	,CAST(X1.Stay/1440 AS varchar(50)) + ' days '
		+ CAST(X1.Stay%1440/60 AS varchar(50)) + ' hours '
		+ CAST(X1.Stay%1440%60 AS varchar(50)) + ' minutes' AS length_of_stay
FROM Results R
	CROSS APPLY( VALUES(DATEDIFF(minute, R.PIn, R.POut)) ) X1 (Stay)
ORDER BY patidid, PIn;

Hi @Ifor for answering this on time. Your solution is working but did not follow thru for patdid 1850 and 1854.It returned the maximum date rather than a null.

Easily adjusted:

WITH Gaps
AS
(
	SELECT S.patidid
		,X.PIn, X.POut
		,CASE
			WHEN LAG(S.datedischarge) OVER (PARTITION BY S.patidid ORDER BY X.PIn)
					= S.preadmitdate
				AND LAG(S.disccode) OVER (PARTITION BY S.patidid ORDER BY X.PIn)
					= 22
				AND S.admitcode = 4
			THEN 0
			ELSE 1
		END AS Gap
	FROM #source S
		CROSS APPLY
		(
			VALUES
			(
				S.preadmitdate + CAST(S.adminttime AS datetime)
				,COALESCE(S.datedischarge + CAST(S.disctime AS datetime), '99991231')
			)
		) X (PIn, POut)
)
,Grps
AS
(
	SELECT patidid
		,PIn, POut
		,SUM(Gap) OVER (PARTITION BY patidid ORDER BY PIn) AS Grp
	FROM Gaps
)
,Results
AS
(
	SELECT patidid
		,MIN(PIn) AS PIn
		,MAX(POut) AS POut
	FROM Grps
	GROUP BY patidid, Grp
)
SELECT R.patidid
	,ROW_NUMBER() OVER (PARTITION BY R.patidid ORDER BY R.PIn) AS episode_c_care_key
	,DATEADD(day, 0, DATEDIFF(day, 0, R.PIn)) AS preadmitdate
	,CAST(CAST(R.PIn AS time) AS varchar(5))  AS adminttime
	,DATEADD(day, 0, DATEDIFF(day, 0, X.POut)) AS datedischarge
	,CAST(CAST(X.POut AS time) AS varchar(5))  AS disctime
	,CAST(X1.Stay/1440 AS varchar(50)) + ' days '
		+ CAST(X1.Stay%1440/60 AS varchar(50)) + ' hours '
		+ CAST(X1.Stay%1440%60 AS varchar(50)) + ' minutes' AS length_of_stay
FROM Results R
	CROSS APPLY( VALUES(NULLIF(R.POut, '99991231')) ) X (POut)
	CROSS APPLY( VALUES(DATEDIFF(minute, R.PIn, X.POut)) ) X1 (Stay)
ORDER BY R.patidid, R.PIn;
2 Likes
		drop table #source
	CREATE TABLE #source(dim_pat_key int, patidid varchar(20),epn int,preadmitdate datetime,adminttime varchar(10),
	admitcode varchar(10),datedischarge datetime,disctime varchar(10),disccode varchar(10))
	INSERT INTO #source VALUES
	---where source is a dimension table with identity key
	(1000,1850,1,'4/23/2020','7:33',1,'6/29/2020','7:30',22)
	,(1001,1850,2,'6/29/2020','9:35',4,'7/8/2020','10:51',7)
	,(1002,1850,3,'7/10/2020','11:51',3,'7/29/2020','9:12',7)
	,(1003,1850,4,'7/31/2020','11:00',2,'8/6/2020','10:24',22)
	,(1004,1850,5,'8/6/2020','12:26',4,null,null,null)
	--select * from #source
	drop table #derivedtable
	CREATE TABLE #derivedtable( patidid varchar(20),episode_c_care_key int,preadmitdate datetime,adminttime varchar(10)
	,datedischarge datetime,disctime varchar(10),LOS int)
	INSERT INTO #derivedtable VALUES
	---where the derived/stage table is a result of a logic.
	---The logic that allows episodes to be condensed into a single continuous care episode 
	---is a discharge code of 22 followed by an admission code of 4 on the same day
	(1850,1,'4/23/2020','7:33','7/8/2020','10:51',76)
	,(1850,2,'7/10/2020','11:51','7/29/2020','9:12',19)
	,(1850,3,'7/31/2020','11:00','2/26/2021','11:04',210)
	--select * from #derivedtable

	--drop table #desiredoutput
	CREATE TABLE #desiredoutput(id int, dim_pat_key int,episode_c_care_key int)
	INSERT INTO #desiredoutput VALUES
			--desired table (bridge table)
	--id an identity field	
	(1,1000,1)
	,(2,1001,1)
	,(3,1002,2)
	,(4,1003,3)
	,(5,1004,3)
	select * from #desiredoutput

@Ifor.Please I m trying to join #source and #derivedtable to get #desiredoutput from my previous response. The output I get is off. What are your suggestions?

Thanks,

If you have to support this you should work through the CTEs to understand what is happening.

The obvious problem is that episode_c_care_key = 3 has not yet finished so the datedischarge should be NULL not 2/26/2021.

WITH Gaps
AS
(
	SELECT S.patidid
		,X.PIn, X.POut
		,CASE
			WHEN LAG(S.datedischarge) OVER (PARTITION BY S.patidid ORDER BY X.PIn)
					= S.preadmitdate
				AND LAG(S.disccode) OVER (PARTITION BY S.patidid ORDER BY X.PIn)
					= 22
				AND S.admitcode = 4
			THEN 0
			ELSE 1
		END AS Gap
	FROM #source S
		CROSS APPLY
		(
			VALUES
			(
				S.preadmitdate + CAST(S.adminttime AS datetime)
				,S.datedischarge + CAST(S.disctime AS datetime)
			)
		) X (PIn, POut)
)
,Grps
AS
(
	SELECT patidid
		,PIn, POut
		,SUM(Gap) OVER (PARTITION BY patidid ORDER BY PIn) AS Grp
	FROM Gaps
)
,Results
AS
(
	SELECT patidid
		,MIN(PIn) AS PIn
		,MAX(ISNULL(POut, CURRENT_TIMESTAMP)) AS POut
	FROM Grps
	GROUP BY patidid, Grp
)
SELECT R.patidid
	,ROW_NUMBER() OVER (PARTITION BY R.patidid ORDER BY R.PIn) AS episode_c_care_key
	,DATEADD(day, 0, DATEDIFF(day, 0, R.PIn)) AS preadmitdate
	,CAST(CAST(R.PIn AS time) AS varchar(5))  AS adminttime
	,DATEADD(day, 0, DATEDIFF(day, 0, NULLIF(R.POut, CURRENT_TIMESTAMP))) AS datedischarge
	,CAST(CAST(NULLIF(R.POut, CURRENT_TIMESTAMP) AS time) AS varchar(5))  AS disctime
	,CAST(X1.Stay/1440 AS varchar(50)) + ' days '
		+ CAST(X1.Stay%1440/60 AS varchar(50)) + ' hours '
		+ CAST(X1.Stay%1440%60 AS varchar(50)) + ' minutes' AS length_of_stay
FROM Results R
	CROSS APPLY(VALUES(DATEDIFF(minute, R.PIn, R.POut))) X1 (Stay)
ORDER BY patidid, PIn;

Once that is fixed you can run code like:

SELECT S.patidid
	,S.epn AS id
	,S.dim_pat_key
	,D.episode_c_care_key
FROM #source S
	JOIN #derivedtable D
		ON S.patidid = D.patidid
			AND S.preadmitdate + S.adminttime <= ISNULL(D.datedischarge, '99991231') + ISNULL(D.disctime, '00:00:00')
			AND ISNULL(S.datedischarge, '99991231') + ISNULL(S.disctime, '00:00:00') >= D.preadmitdate + D.adminttime
ORDER BY patidid, id;
1 Like

@Ifor
I understand the code.
The requirement actually is for the current episode with a null date to be replaced with a getdate(), the value of 2/26/2021. was the last time i ran my code.
But I am getting the value that is needed.

Thanks, a lot you have been very helpful