SQLTeam.com | Weblogs | Forums

Calculating Gaps in day by enrollment segments

I have an issue with some code that I am writing. I am trying to calculate the min and max effective dates for waivers so I can roll up continuous enrollment segments. I have the continuous enrollment part working but now I am trying to calculate any gaps in between segments (in case they are not continuous) by creating a start_gap and end_gap date along with the calculated days between the gap.

For some reason, I am getting a calculated next day as a start_gap when there is not a gap, the segment has ended. A start_gap should only be calculated when there is another segment following that is not on the next day.

Here is my sample data:

CREATE TABLE #temp(
MED_ID varchar(20),
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 ','4/1/2017','1/7/2019','NULL','NULL');
INSERT INTO #temp (MED_ID,QNXT_WAIVER_ATTRIBUTE,EFFDATE,TERMDATE,) VALUES ('123456','SKILLED CARE ','1/8/2019','2/2/2019','2/3/2019','NULL');

My code is as follows:
IF OBJECT_ID('tempdb..#TEMP','U') IS NOT NULL DROP TABLE #TEMP;
SELECT d.MED_ID
, d.QNXT_WAIVER_ATTRIBUTE
, EFFDATE = CAST(MIN(d.EFFDATE) AS DATE)
, TERMDATE = CAST(MAX(d.TERMDATE) AS DATE)

FROM (
SELECT *, Grouper = DATEADD(DAY, 0 - SUM(1 + DATEDIFF(DAY, EFFDATE, TERMDATE))
OVER(PARTITION BY MED_ID,QNXT_WAIVER_ATTRIBUTE ORDER BY EFFDATE)
, TERMDATE)
FROM #temp

) AS d
GROUP BY d.MED_ID, d.QNXT_WAIVER_ATTRIBUTE, d.Grouper
ORDER BY d.MED_ID, MIN(d.EFFDATE);

;WITH s AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY MED_ID ORDER BY EFFDATE)rn
FROM #TEMP)
SELECT a.MED_ID, a.QNXT_WAIVER_ATTRIBUTE, CAST(a.EFFDATE AS DATE) AS EFFDATE,
CAST(a.TERMDATE AS DATE) AS TERMDATE,

	CASE WHEN CAST(DATEADD(DAY, 1, MAX(a.TERMDATE) OVER (PARTITION BY a.MED_ID ORDER BY a.EFFDATE)) AS DATE) = b.EFFDATE THEN NULL
			WHEN a.TERMDATE = '12/31/2078' THEN  NULL 

			WHEN b.rn = NULL THEN NULL
			ELSE CAST(DATEADD(DAY, 1, MAX(a.TERMDATE) OVER (PARTITION BY
				 a.MED_ID ORDER BY a.EFFDATE)) AS DATE) END AS START_GAP,
				CASE WHEN CAST(DATEADD(DAY,-1,LEAD(a.EFFDATE) OVER (PARTITION BY a.MED_ID
			ORDER BY a.EFFDATE)) AS DATE) = a.TERMDATE THEN NULL 
			WHEN  a.TERMDATE = '12/31/2078' THEN NULL
			ELSE CAST(DATEADD
			(DAY,-1,LEAD(a.EFFDATE) OVER (PARTITION BY a.MED_ID ORDER BY a.EFFDATE))
			AS DATE) END AS END_GAP

			from s a
JOIN s b on b.MED_ID = a.MED_ID AND b.RN = a.RN+1  

order by med_id, effdate

Blockquote

This is giving me a start_gap of 2/3/2019 when it should be null.
Also, I need to calculate the days between the gaps. If the next segment starts on the next day, there is no gap, but if it starts after 2 days then start_gap would be the same day as the end_gap and the gap in days would be 1.

Thanks for any help!