November 22, 2017 at 9:06 am
I have a requirement where 3 records *should* be getting recorded on the patient at the same time. However, occasionally one of those records does not come through and when that happens, I need to look back to the nearest previous record and evaluate that record for a value of condition A or condition B. When condition B is true then indicate a Y in a new column beside the rest of the data set.
Here is some sample code I set up with some comments that I hope are clear.
Here is a link I found that drove me in the direction of using LAG with a 'Variable Jump Back'.
LINK
I really appreciate you help and advise!
CREATE TABLE #Y
(
PAT_ID INT
,RECORD_TYPE VARCHAR(50)
,VALUE VARCHAR(50)
,RECORDING_DTM DATETIME
);
INSERT #Y ( PAT_ID , RECORD_TYPE , VALUE , RECORDING_DTM )
VALUES
/* WHEN WE HAVE ALL THREE HAVING OCCURED AT THE SAME TIME THEN CHECK TO SEE IF TEMP = "VENT" IF SO THEN 'Y' ELSE 'N' */
(36588789, 'BLOOD PRESSURE', '120', '2017-01-15 08:13:00' ),
(36588789, 'HEART RATE', '60', '2017-01-15 08:13:00' ),
(36588789, 'TEMP', '98', '2017-01-15 08:13:00' ),
(36588789, 'BLOOD PRESSURE', '110', '2017-01-15 08:50:00' ),
(36588789, 'HEART RATE', '60', '2017-01-15 08:50:00' ),
(36588789, 'TEMP', '98', '2017-01-15 08:50:00' ),
(36588789, 'HEART RATE', '61', '2017-01-15 08:52:00' ), -- 'N'
(36588789, 'HEART RATE', '62', '2017-01-15 08:53:00' ), -- 'N'
(36588789, 'HEART RATE', '62', '2017-01-15 08:54:00' ), -- 'N'
/* CHECK THE NEAREST LAG TEMP VALUE WHEN WE ARE MISSING TEMP/VENT-MODE BUT HAVE A HEART RATE RECORDED TOGETHER AT THE SAME TIME. SO GET THE PREVIOUS VALUE ( 98 ) AND IF THE PREVIOUS VALUE = "VENT" THEN 'Y' IN NEW COLUMN ELSE 'N'. */
(36588789, 'BLOOD PRESSURE', '115', '2017-01-16 08:08:00' ),
(36588789, 'HEART RATE', '60', '2017-01-16 08:08:00' ),
--(36588789, 'TEMP', '98', '2017-01-16 08:50:00' ), -- PREVIOUS <> 'VENT' SO 'N'
/* UP TO THIS POINT ALL CALCULATED VALUES IN DERIVED COLUMN SHOULD BE 'N' */
/* NOW LETS GET SOME 'Y' */
(36588789, 'HEART RATE', '65', '2017-01-17 09:52:00' ),
(36588789, 'HEART RATE', '65', '2017-01-17 09:53:00' ),
(36588789, 'HEART RATE', '63', '2017-01-17 09:54:00' ),
(36588789, 'BLOOD PRESSURE', '120', '2017-01-18 10:50:00' ),
(36588789, 'HEART RATE', '62', '2017-01-18 10:50:00' ),
(36588789, 'TEMP', 'VENT', '2017-01-18 10:50:00' ),
/* CHECK THE NEAREST LAG TEMP VALUE WHEN WE ARE MISSING TEMP/VENT-MODE BUT HAVE A HEART RATE RECORDED TOGETHER AT THE SAME TIME. SO GET THE PREVIOUS VALUE ( 98 ) AND IF THE PREVIOUS VALUE = "VENT" THEN 'Y' IN NEW COLUMN ELSE 'N'. */
(36588789, 'BLOOD PRESSURE', '120', '2017-01-19 08:08:00' ),
(36588789, 'HEART RATE', '61', '2017-01-19 08:08:00' ),
--(36588789, 'TEMP', '98', '2017-01-19 08:50:00' ), -- PREVIOUS 'TEMP' = 'VENT' ON '2017-01-18 10:50:00' SO 'Y'.
/* NEW PATIENT - SAME DATA FOR SAMPLE */
(36588892, 'BLOOD PRESSURE', '120', '2017-02-15 08:13:00' ),
(36588892, 'HEART RATE', '60', '2017-02-15 08:13:00' ),
(36588892, 'TEMP', '98', '2017-02-15 08:13:00' ),
(36588892, 'BLOOD PRESSURE', '110', '2017-02-15 08:50:00' ),
(36588892, 'HEART RATE', '60', '2017-02-15 08:50:00' ),
(36588892, 'TEMP', '98', '2017-02-15 08:50:00' ),
(36588892, 'HEART RATE', '61', '2017-02-15 08:52:00' ), -- 'N'
(36588892, 'HEART RATE', '62', '2017-02-15 08:53:00' ), -- 'N'
(36588892, 'HEART RATE', '62', '2017-02-15 08:54:00' ), -- 'N'
/* CHECK THE NEAREST LAG TEMP VALUE WHEN WE ARE MISSING TEMP/VENT-MODE BUT HAVE A HEART RATE RECORDED TOGETHER AT THE SAME TIME. SO GET THE PREVIOUS VALUE ( 98 ) AND IF THE PREVIOUS VALUE = "VENT" THEN 'Y' IN NEW COLUMN ELSE 'N'. */
(36588892, 'BLOOD PRESSURE', '115', '2017-02-16 08:08:00' ),
(36588892, 'HEART RATE', '60', '2017-02-16 08:08:00' ),
--(36588892, 'TEMP', '98', '2017-01-16 08:50:00' ), -- PREVIOUS <> 'VENT' SO 'N'
/* UP TO THIS POINT ALL CALCULATED VALUES IN DERIVED COLUMN SHOULD BE 'N' */
/* NOW LETS GET SOME 'Y' */
(36588892, 'HEART RATE', '65', '2017-02-17 09:52:00' ),
(36588892, 'HEART RATE', '65', '2017-02-17 09:53:00' ),
(36588892, 'HEART RATE', '63', '2017-02-17 09:54:00' ),
(36588892, 'BLOOD PRESSURE', '120', '2017-02-18 10:50:00' ),
(36588892, 'HEART RATE', '62', '2017-02-18 10:50:00' ),
(36588892, 'TEMP', 'VENT', '2017-02-18 10:50:00' ),
/* CHECK THE NEAREST LAG TEMP VALUE WHEN WE ARE MISSING TEMP/VENT-MODE BUT HAVE A HEART RATE RECORDED TOGETHER AT THE SAME TIME. SO GET THE PREVIOUS VALUE ( 98 ) AND IF THE PREVIOUS VALUE = "VENT" THEN 'Y' IN NEW COLUMN ELSE 'N'. */
(36588892, 'BLOOD PRESSURE', '120', '2017-02-19 08:08:00' ),
(36588892, 'HEART RATE', '61', '2017-02-19 08:08:00' )
--(36588892, 'TEMP', '98', '2017-02-19 08:50:00' ), -- PREVIOUS 'TEMP' = 'VENT' ON '2017-01-18 10:50:00' SO 'Y'.
So far I have:
/* this is about how far I got with determining the set of 3 entries within the same time */
;WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY RECORDING_DTM ORDER BY RECORD_TYPE) RN,
COUNT(*) OVER (PARTITION BY RECORDING_DTM) CNT,
'' AS INDICATOR_COLUMN
FROM #Y
GROUP BY #Y.RECORDING_DTM, #Y.PAT_ID, #Y.RECORD_TYPE, #Y.VALUE
)
SELECT *
FROM CTE
November 22, 2017 at 9:57 am
This isn't very elegant, but it seems to work!
WITH Pivoted AS (
SELECT
PAT_ID
, RECORDING_DTM
, CASE
WHEN RECORD_TYPE = 'BLOOD PRESSURE' THEN VALUE
ELSE ''
END AS BP
, CASE
WHEN RECORD_TYPE = 'HEART RATE' THEN VALUE
ELSE ''
END AS Pulse
, CASE
WHEN RECORD_TYPE = 'TEMP' THEN VALUE
ELSE ''
END AS Temp
FROM #Y
)
, Pivoted2 AS (
SELECT
PAT_ID
, RECORDING_DTM
, MAX(BP) AS BP
, MAX(Pulse) AS Pulse
, MAX(Temp) AS Temp
FROM Pivoted
GROUP BY
PAT_ID
, RECORDING_DTM
)
, TempNumbered AS (
SELECT
PAT_ID
, RECORDING_DTM
, BP
, Pulse
, Temp
, CASE WHEN Temp > ''
THEN SUM(CASE WHEN Temp > '' THEN 1 ELSE 0 END) OVER (PARTITION BY PAT_ID ORDER BY PAT_ID ROWS UNBOUNDED PRECEDING)
ELSE 0
END AS TempNo
, ROW_NUMBER() OVER (PARTITION BY PAT_ID ORDER BY RECORDING_DTM) AS RowNo
FROM Pivoted2
)
, TempSubstituted AS (
SELECT
PAT_ID
, RECORDING_DTM
, BP
, Pulse
, CASE
WHEN BP > '' AND Pulse > '' AND Temp = '' THEN LAG(Temp,RowNo-TempNo,'') OVER (PARTITION BY PAT_ID ORDER BY RowNo)
WHEN BP = '' OR Pulse = '' THEN ''
ELSE Temp
END AS Temp
FROM TempNumbered
)
SELECT
PAT_ID
, RECORDING_DTM
, BP
, Pulse
, Temp
, CASE
WHEN BP = '' OR Pulse = '' OR Temp <> 'Vent' THEN 'N'
ELSE 'Y'
END AS INDICATOR_COLUMN
FROM TempNumbered
John
November 22, 2017 at 10:22 am
Spoke too soon.
Looking at the result set:
Row 11 should have 'Y' since previous Temp was 'VENT'.
and
Row 22 should have 'Y' since previous Temp was 'VENT'.
November 22, 2017 at 10:56 am
You didn't say what results you were expecting. John pivoted the table, but I did not.
The issue you were having duplicating the article is that it depends on having a NULL value in the field that you are trying to smear. You did not have that. Here is how I implemented the article.
;
WITH Temps AS
(
SELECT *, COUNT(Temp) OVER(PARTITION BY PAT_ID ORDER BY RECORDING_DTM, RECORD_TYPE DESC) c
FROM #Y
CROSS APPLY ( VALUES(CASE WHEN RECORD_TYPE = 'TEMP' THEN VALUE END) ) t(Temp)
)
SELECT PAT_ID, RECORD_TYPE, VALUE, RECORDING_DTM, CASE WHEN MAX(Temp) OVER(PARTITION BY PAT_ID, c) = 'VENT' THEN 'Y' ELSE 'N' END
FROM Temps
ORDER BY PAT_ID, RECORDING_DTM, RECORD_TYPE
;
That being said, Itzik Ben Gan devised an even better way to smear data. Here is that approach.
;
SELECT PAT_ID, RECORD_TYPE, VALUE, RECORDING_DTM, CASE WHEN SUBSTRING(MAX(b.bin) OVER( PARTITION BY PAT_ID ORDER BY RECORDING_DTM, RECORD_TYPE DESC ROWS UNBOUNDED PRECEDING), 9, 50) = CAST('VENT' AS BINARY(50)) THEN 'Y' ELSE 'N' END
FROM #Y
CROSS APPLY ( VALUES(CAST(RECORDING_DTM AS BINARY(8)) + CAST(CASE WHEN RECORD_TYPE = 'TEMP' THEN VALUE END AS BINARY(50))) ) b(bin)
ORDER BY PAT_ID, RECORDING_DTM, RECORD_TYPE
;
And here is a comparison of the results:
/* Smear 1 */
Table 'Worktable'. Scan count 43, logical reads 330, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Y___000000027534'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
/* Smear 2 */
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Y___000000027534'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 22, 2017 at 11:26 am
Thanks Drew! I'll have a look.
November 22, 2017 at 11:32 am
I think you nailed it Drew! Thank you for both references.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply