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

Transact-SQL
1

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)

output_desired_result
output_desired_result867×332 9.09 KB

2 
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;
3

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.

4

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
5 
		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
6

@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,

7

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;
2 Likes
8

@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

9

Hi @Ifor ,
Thanks for been a tremendous help thus far.
Please any ideas on how I can achieve the result shown in the attached picture .For now I have all the rows of the error field initiated with a default value of '0' and update the field based on different conditions but for the result .Its a bit challenging since its based on comparing 2 rows.
**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. So if a discharged code of 22 is followed by a different code other than an admission code of 4 for the same day that is an invalid entry.

error_trapping
error_trapping1221×194 7.67 KB