Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
-- 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