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!