Getting Min and Max dates by segment for continuous enrollment

Transact-SQL
1

Hi there,

I am trying to find the min and max effective and term dates for each waiver per person. Easier to show an example:

In the following sample data, for each segment of continuous enrollment per member, per waiver...

img_5d9655d92a8c8.png709×121 4.01 KB

I am looking to see

ELDERLY OR DISABLED WITH CONSUMER DIRECTION 7/1/2015 2/7/2019

NURSING FACILITY CARE 2/8/2019 3/12/2019

ELDERLY OR DISABLED WITH CONSUMER DIRECTION 5/24/2019 12/31/2078

This is the code I have so far but It isnt working like I need it to. Can anyone help?

Here is sample data:

create table #temp
MED_ID varchar(10),
QNXT_WAIVER_ATTRIBUTE varchar(255),
EFFDATE date,
TERMDATE date)

INSERT INTO #temp (MED_ID,QNXT_Waiver_Attribute,effdate,termdate) VALUES ('123456','ELDERLY OR DISABLED WAIVER WITH CONSUMER DIRECTION ','7/1/2015','4/30/2018');
INSERT INTO #temp (MED_ID,QNXT_Waiver_Attribute,effdate,termdate) VALUES ('123456','ELDERLY OR DISABLED WAIVER WITH CONSUMER DIRECTION ','5/1/2018','2/7/2019');
INSERT INTO #temp (MED_ID,QNXT_Waiver_Attribute,effdate,termdate) VALUES ('123456','NURSING FACILITY CARE ','2/8/2019','3/12/2019');
INSERT INTO #temp (MED_ID,QNXT_Waiver_Attribute,effdate,termdate) VALUES ('123456','ELDERLY OR DISABLED WAIVER WITH CONSUMER DIRECTION ','5/24/2019','7/31/2019');
INSERT INTO #temp (MED_ID,QNXT_Waiver_Attribute,effdate,termdate) VALUES ('123456','ELDERLY OR DISABLED WAIVER WITH CONSUMER DIRECTION ','8/1/2019','12/31/2078');

AND here is the code I have so far: As you can see, it is not picking up the correct termdate for the first segment, and the Nursing Facility waiver is excluded completely.

SELECT s1.MED_ID,
s1.QNXT_WAIVER_ATTRIBUTE,
s1.EFFDATE,
Min(s1.TERMDATE) AS Term_Date,
ROW_NUMBER() OVER(ORDER BY s1.EFFDATE) AS Sequence_ID
FROM #temp s1
INNER JOIN #temp t1 ON t1.MED_ID=s1.MED_ID
AND s1.EFFDATE <= t1.TERMDATE
AND NOT EXISTS(SELECT * FROM #TEMP t2
WHERE t2.MED_ID=t1.MED_ID
AND (t1.TERMDATE+1) >= t2.EFFDATE AND t1.TERMDATE < t2.TERMDATE)
WHERE NOT EXISTS(SELECT * FROM #TEMP s2
WHERE s2.MED_ID=s1.MED_ID AND s1.EFFDATE > s2.EFFDATE AND (s1.EFFDATE-1) <= s2.TERMDATE)
AND t1.QNXT_WAIVER_ATTRIBUTE = s1.QNXT_Waiver_Attribute
GROUP BY s1.MED_ID,s1.QNXT_Waiver_Attribute, s1.EFFDATE
ORDER BY s1.MED_ID,s1.QNXT_Waiver_Attribute,s1.EFFDATE;

2

If you don't have any break in dates for consecutive QNXT_Waiver_Attribute unless there is an intervening QNXT_Waiver_Attribute that is different, then you can query like this.

;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY MED_ID,QNXT_Waiver_Attribute ORDER BY effdate) -
		ROW_NUMBER() OVER (PARTITION BY MED_ID ORDER BY effdate) AS GroupNumber
	FROM
		#temp
)
SELECT
	MED_ID,
	QNXT_Waiver_Attribute,
	MIN(EFFDATE) AS EFFDate,
	MAX(TERMDATE) AS TERMDate
FROM	
	cte c
GROUP BY
	c.GroupNumber,
	MED_ID,
	QNXT_Waiver_Attribute
ORDER BY
	EFFDate;

But if you did have breaks, for example, in the first row in your example, the TERMDATE was earlier than 2018-04-30, for example say 2018-03-15, then this query would not pick up that break. If you want to account for that as well, you should left join to a calendar table which has one row for each calendar date, and then use a similar grouping method, except you would partition by the calendar date as well.