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

drug_exposure_eras_demo_NoUnboundPreceeding

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