Last active
January 16, 2019 16:26
-
-
Save chrisknoll/8d3c6744bae4f060aec1 to your computer and use it in GitHub Desktop.
This SQL is simiar to the drugEraBuilder_demo, but does not use the ROWS UNBOUNDED PRECEDING windowing function. This makes this script work in databases where row_number() exists but doesn't support ROWS UNBOUNDED PRECEDING
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Adapted from calculating concurrent sessions query found at http://sqlmag.com/t-sql/calculating-concurrent-sessions-part-3 | |
-- Credits to Ben Flanaghan, Arnold Fribble, and R. Barry Young | |
-- DROP TABLE #DRUG_EXPOSURE; | |
CREATE TABLE #DRUG_EXPOSURE | |
( | |
[DRUG_EXPOSURE_ID] [bigint] NOT NULL, | |
[PERSON_ID] [bigint] NOT NULL, | |
[DRUG_CONCEPT_ID] [int] NOT NULL, | |
[DRUG_EXPOSURE_START_DATE] [date] NOT NULL, | |
[DRUG_TYPE_CONCEPT_ID] [int] NOT NULL, | |
[REFILLS] [int] NULL, | |
[QUANTITY] [int] NULL, | |
[DAYS_SUPPLY] [int] NULL, | |
[DRUG_EXPOSURE_END_DATE] [date] NULL | |
) ON [PRIMARY] | |
GO | |
-- Person 1000000 | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000000,2000000,'1/1/2010','1/15/2010',null,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000000,2000000,'1/13/2010',null,10,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000000,2000001,'1/25/2010','2/3/2010',null,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000000,2000002,'4/1/2010','5/15/2010',null,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000000,2000003,'4/10/2010','4/15/2010',null,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000000,2000004,'5/13/2010',null,15,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000000,2000005,'9/1/2010',null,10,3000000,1,1); | |
-- Person 1000001: | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000001,2000000,'1/1/2010','1/05/2010',null,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000001,2000001,'1/21/2010',null,10,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000001,2000002,'4/1/2010','4/15/2010',null,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000001,2000003,'8/1/2010',null,30,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000001,2000004,'10/1/2010','10/5/2010',null,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000001,2000005,'10/3/2010','10/15/2010',null,3000000,1,1); | |
INSERT INTO #DRUG_EXPOSURE ([DRUG_EXPOSURE_ID],[PERSON_ID],[DRUG_CONCEPT_ID],[DRUG_EXPOSURE_START_DATE],[DRUG_EXPOSURE_END_DATE],[DAYS_SUPPLY],[DRUG_TYPE_CONCEPT_ID],[REFILLS],[QUANTITY]) | |
VALUES (0,1000001,2000006,'11/30/2010',null,5,3000000,1,1); | |
with T as | |
( | |
select *, ROW_NUMBER() OVER (order by DRUG_EXPOSURE_ID) as rn | |
from #DRUG_EXPOSURE | |
) | |
UPDATE T set DRUG_EXPOSURE_ID = rn; | |
-- here is the raw exposure data | |
select * from #DRUG_EXPOSURE; | |
-- pretend that the data in #DRUG_EXPOSURE is the set or rows that match a certain descent concept ID. | |
with cteDrugTarget (DRUG_EXPOSURE_ID, PERSON_ID, DRUG_CONCEPT_ID, DRUG_EXPOSURE_START_DATE, DRUG_EXPOSURE_END_DATE, DRUG_TYPE_CONCEPT_ID, REFILLS, QUANTITY, DAYS_SUPPLY, RowNum) as | |
( | |
-- Normalize DRUG_EXPOSURE_END_DATE to either the existing drug exposure end date, or add days supply, or add 1 day to the start date | |
select d.DRUG_EXPOSURE_ID, d. PERSON_ID, DRUG_CONCEPT_ID, DRUG_EXPOSURE_START_DATE, | |
COALESCE(DRUG_EXPOSURE_END_DATE, DATEADD(day,DAYS_SUPPLY,DRUG_EXPOSURE_START_DATE), DATEADD(day,1,DRUG_EXPOSURE_START_DATE)) as DRUG_EXPOSURE_END_DATE, | |
d.DRUG_TYPE_CONCEPT_ID, d.REFILLS, d.QUANTITY, d.DAYS_SUPPLY, | |
ROW_NUMBER() OVER (PARTITION BY d.PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as RowNum | |
FROM #DRUG_EXPOSURE d | |
), | |
cteEndDates (PERSON_ID, END_DATE) as -- the magic | |
( | |
select PERSON_ID, DATEADD(day,-30,EVENT_DATE) as END_DATE -- unpad the end date | |
FROM | |
( | |
select E1.PERSON_ID, E1.EVENT_DATE, COALESCE(E1.START_ORDINAL,MAX(E2.START_ORDINAL)) START_ORDINAL, E1.OVERALL_ORD | |
FROM | |
( | |
select PERSON_ID, EVENT_DATE, EVENT_TYPE, | |
--MAX(START_ORDINAL) OVER (PARTITION BY PERSON_ID ORDER BY EVENT_DATE, EVENT_TYPE ROWS UNBOUNDED PRECEDING) as START_ORDINAL, -- this pulls the current START down from the prior rows so that the NULLs from the END DATES will contain a value we can compare with | |
START_ORDINAL, | |
ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY EVENT_DATE, EVENT_TYPE) AS OVERALL_ORD -- this re-numbers the inner UNION so all rows are numbered ordered by the event date | |
from | |
( | |
-- select the start dates, assigning a row number to each | |
Select PERSON_ID, DRUG_EXPOSURE_START_DATE AS EVENT_DATE, 0 as EVENT_TYPE, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as START_ORDINAL | |
from cteDrugTarget | |
UNION ALL | |
-- add the end dates with NULL as the row number, padding the end dates by 30 to allow a grace period for overlapping ranges. | |
select PERSON_ID, DATEADD(day,30,DRUG_EXPOSURE_END_DATE), 1 as EVENT_TYPE, NULL | |
FROM cteDrugTarget | |
) RAWDATA | |
) E1 | |
JOIN ( | |
Select PERSON_ID, DRUG_EXPOSURE_START_DATE AS EVENT_DATE, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as START_ORDINAL | |
from cteDrugTarget | |
) E2 ON E1.PERSON_ID = E2.PERSON_ID AND E2.EVENT_DATE <= E1.EVENT_DATE | |
GROUP BY E1.PERSON_ID, E1.EVENT_DATE, E1.START_ORDINAL, E1.OVERALL_ORD | |
) E | |
WHERE 2 * E.START_ORDINAL - E.OVERALL_ORD = 0 | |
) | |
select * from cteEndDates; | |
-- now bring it all together by joining the calcuated end dates back to the #drugExposure table, and the row's exposure's era end date will be the MIN end date that is >= the exposure date. | |
with cteDrugTarget (DRUG_EXPOSURE_ID, PERSON_ID, DRUG_CONCEPT_ID, DRUG_EXPOSURE_START_DATE, DRUG_EXPOSURE_END_DATE, DRUG_TYPE_CONCEPT_ID, REFILLS, QUANTITY, DAYS_SUPPLY, RowNum) as | |
( | |
-- Normalize DRUG_EXPOSURE_END_DATE to either the existing drug exposure end date, or add days supply, or add 1 day to the start date | |
select d.DRUG_EXPOSURE_ID, d. PERSON_ID, DRUG_CONCEPT_ID, DRUG_EXPOSURE_START_DATE, | |
COALESCE(DRUG_EXPOSURE_END_DATE, DATEADD(day,DAYS_SUPPLY,DRUG_EXPOSURE_START_DATE), DATEADD(day,1,DRUG_EXPOSURE_START_DATE)) as DRUG_EXPOSURE_END_DATE, | |
d.DRUG_TYPE_CONCEPT_ID, d.REFILLS, d.QUANTITY, d.DAYS_SUPPLY, | |
ROW_NUMBER() OVER (PARTITION BY d.PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as RowNum | |
FROM #DRUG_EXPOSURE d | |
), | |
cteEndDates (PERSON_ID, END_DATE) as -- the magic | |
( | |
select PERSON_ID, DATEADD(day,-30,EVENT_DATE) as END_DATE -- unpad the end date | |
FROM | |
( | |
select E1.PERSON_ID, E1.EVENT_DATE, COALESCE(E1.START_ORDINAL,MAX(E2.START_ORDINAL)) START_ORDINAL, E1.OVERALL_ORD | |
FROM | |
( | |
select PERSON_ID, EVENT_DATE, EVENT_TYPE, | |
--MAX(START_ORDINAL) OVER (PARTITION BY PERSON_ID ORDER BY EVENT_DATE, EVENT_TYPE ROWS UNBOUNDED PRECEDING) as START_ORDINAL, -- this pulls the current START down from the prior rows so that the NULLs from the END DATES will contain a value we can compare with | |
START_ORDINAL, | |
ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY EVENT_DATE, EVENT_TYPE) AS OVERALL_ORD -- this re-numbers the inner UNION so all rows are numbered ordered by the event date | |
from | |
( | |
-- select the start dates, assigning a row number to each | |
Select PERSON_ID, DRUG_EXPOSURE_START_DATE AS EVENT_DATE, 0 as EVENT_TYPE, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as START_ORDINAL | |
from cteDrugTarget | |
UNION ALL | |
-- add the end dates with NULL as the row number, padding the end dates by 30 to allow a grace period for overlapping ranges. | |
select PERSON_ID, DATEADD(day,30,DRUG_EXPOSURE_END_DATE), 1 as EVENT_TYPE, NULL | |
FROM cteDrugTarget | |
) RAWDATA | |
) E1 | |
LEFT JOIN ( | |
Select PERSON_ID, DRUG_EXPOSURE_START_DATE AS EVENT_DATE, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as START_ORDINAL | |
from cteDrugTarget | |
) E2 ON E1.PERSON_ID = E2.PERSON_ID AND E2.EVENT_DATE < E1.EVENT_DATE | |
GROUP BY E1.PERSON_ID, E1.EVENT_DATE, E1.START_ORDINAL, E1.OVERALL_ORD | |
) E | |
WHERE 2 * E.START_ORDINAL - E.OVERALL_ORD = 0 | |
) | |
select d.DRUG_EXPOSURE_ID, | |
d.PERSON_ID, | |
d.DRUG_CONCEPT_ID, | |
d.DRUG_EXPOSURE_START_DATE, | |
d.DRUG_EXPOSURE_END_DATE, | |
MIN(e.END_DATE) as ERA_END_DATE, | |
d.DRUG_TYPE_CONCEPT_ID, | |
d.REFILLS, | |
d.QUANTITY, | |
d.DAYS_SUPPLY, | |
d.RowNum | |
FROM cteDrugTarget d | |
JOIN cteEndDates e on d.PERSON_ID = e.PERSON_ID and e.END_DATE >= d.DRUG_EXPOSURE_START_DATE | |
GROUP BY d.DRUG_EXPOSURE_ID, | |
d.PERSON_ID, | |
d.DRUG_CONCEPT_ID, | |
d.DRUG_EXPOSURE_START_DATE, | |
d.DRUG_EXPOSURE_END_DATE, | |
d.DRUG_TYPE_CONCEPT_ID, | |
d.REFILLS, | |
d.QUANTITY, | |
d.DAYS_SUPPLY, | |
d.RowNum | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment