Reputation: 5
I'm trying to figure out how the ROWS clause works in the OVER function. I'm using this query to test the logic, the CASE WHEN section is part of a larger query I'm working on, but for now I want to focus on this small section to understand. I'm working with healthcare data, and basically want to check there is a certain type of visit in the past 2 rows from the current row.
Here is the query:
SELECT visit_no, mr_no, admit_date, dischg_date, pat_status,
CASE WHEN
LAG(pat_status) OVER(PARTITION BY mr_no ORDER BY admit_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )
LIKE '%IC%'
THEN 'Y' END AS pat_status_check
FROM chmadmtr
WHERE mr_no = '508249'
and outcome:
visit_no | mr_no | admit_date | dischg_date | pat_status | pat_status_check |
---|---|---|---|---|---|
7047395 | 508249 | 2019-08-06 | 2019-08-10 | AIM | (NULL) |
7047983 | 508249 | 2019-08-12 | 2019-08-19 | AIC | (NULL) |
7049597 | 508249 | 2019-08-27 | 2019-08-29 | AIC | (Y) |
7052596 | 508249 | 2019-08-27 | 2019-10-01 | AIM | (Y) |
7052823 | 508249 | 2019-10-01 | 2019-10-01 | AOE | (NULL) |
7054072 | 508249 | 2020-10-16 | 2019-10-22 | AIC | (NULL) |
7055128 | 508249 | 2019-10-29 | 2019-11-01 | AIC | (Y) |
7065066 | 508249 | 2020-02-28 | 2019-03-05 | AIC | (Y) |
7066857 | 508249 | 2020-03-21 | 2020-03-23 | AIM | (Y) |
7066961 | 508249 | 2020-03-23 | 2020-03-25 | AIY | (NULL) |
The last row is where my issue is, from my understanding this query should list the visit ID partitioned by medical record number (mr_no), order them by admit_date and check two rows behind the current row to see if any of the visit types (pat_status) contains "IC", so the pat_status_check should be "Y" for the last row. But it seems like no matter what number I put in the "n PRECEDING", it only checks 1 previous row to see if it contains "IC". Any help or insight is appreciated.
Upvotes: 0
Views: 637
Reputation: 31832
First change your frame to ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
, since you don't want to include the current row.
Now you can use NTH_VALUE()
for the 1st and 2nd row in the frame:
SELECT visit_no, mr_no, admit_date, dischg_date, pat_status,
CASE WHEN
NTH_VALUE(pat_status, 1) OVER w LIKE '%IC%'
OR
NTH_VALUE(pat_status, 2) OVER w LIKE '%IC%'
THEN 'Y' END AS pat_status_check
FROM chmadmtr WHERE mr_no = '508249'
WINDOW w AS (
PARTITION BY mr_no
ORDER BY admit_date
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
)
Or use JSON_ARRAYARG
to aggregate the frame values into a single (JSON) string:
SELECT visit_no, mr_no, admit_date, dischg_date, pat_status,
CASE WHEN
JSON_ARRAYAGG(pat_status) OVER w LIKE '%IC%'
THEN 'Y' END AS pat_status_check
FROM chmadmtr WHERE mr_no = '508249'
WINDOW w AS (
PARTITION BY mr_no
ORDER BY admit_date
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
)
Upvotes: 0