Getting Min and Max dates by segment for continuous enrollment

Question

Getting Min and Max dates by segment for continuous enrollment

shel 29143

Mr or Mrs. 500

Points: 502
More actions
October 3, 2019 at 8:18 pm

#3685840

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

This is a variation on packing intervals. This approach assumes that there are no gaps. You need a slightly different approach if gaps are possible.

WITH status_starts AS
(
SELECT *, CASE WHEN LAG(QNXT_WAIVER_ATTRIBUTE, 1, '') OVER(PARTITION BY MED_ID ORDER BY EFFDATE) <> QNXT_WAIVER_ATTRIBUTE THEN 1 ELSE 0 END AS new_group
FROM #temp
)
, status_groups AS
(
SELECT *, SUM(s.new_group) OVER(PARTITION BY s.MED_ID ORDER BY s.EFFDATE ROWS UNBOUNDED PRECEDING) AS group_num
FROM status_starts AS s
)
SELECT sg.MED_ID, sg.QNXT_WAIVER_ATTRIBUTE, MIN(sg.EFFDATE) AS EFFDATE, MAX(sg.TERMDATE) AS TERMDATE
FROM status_groups sg
GROUP BY sg.MED_ID, sg.QNXT_WAIVER_ATTRIBUTE, sg.group_num
ORDER BY sg.MED_ID, sg.group_num

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

DesNorton SSC-Insane Points: 24382 More actions · Answer 2

--=== Logic breakdown ================================================
--===   -- Group the consecutive enrollments for each waiver
--=== SELECT *, Grouper = DATEADD(DAY,0-DurationRuningTotal,TERMDATE)
--=== FROM (
--===   -- Get the enrollment duration running total for each waiver
--===  SELECT *, DurationRuningTotal = SUM(Duration) OVER(PARTITION BY MED_ID, QNXT_WAIVER_ATTRIBUTE ORDER BY EFFDATE)
--===  FROM (
--===   -- Get the duration of each enrollment
--===   SELECT *, Duration = 1 +DATEDIFF(DAY, EFFDATE, TERMDATE) 
--===   FROM #TEMP
--===  ) d1 
--=== ) d2
--=== ORDER BY MED_ID, EFFDATE;

--=== Now build it as a single statement

SELECT d.MED_ID
     , d.QNXT_WAIVER_ATTRIBUTE
     , EFFDATE  = MIN(d.EFFDATE)
     , TERMDATE = MAX(d.TERMDATE)
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);

How to post data/code on a forum to get the best help.

shel 29143 Mr or Mrs. 500 Points: 502 More actions · Answer 3

Hi Drew, gaps are possible. Do you know how I would account for gaps in the code? I really appreciate your help I have been working on this all day and just haven’t been able to get it. Thanks for your reply.

shel 29143 Mr or Mrs. 500 Points: 502 More actions · Answer 4

Thanks so much DesNorton. I real appreciate your reply. I am looking forward to trying both these approaches.

shel 29143 Mr or Mrs. 500 Points: 502 More actions · Answer 5

Drew,

Holy cow this worked perfectly! Could you walk me through the logic so I can understand exactly why this works? Much appreciated!

Shel