Calculating gaps between enrollment segments

Question

Calculating gaps between enrollment segments

shel 29143

Mr or Mrs. 500

Points: 502
More actions
October 4, 2019 at 3:19 pm

#3686071
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
- This topic was modified 5 years, 11 months ago by shel 29143.

Viewing 2 posts - 1 through 2 (of 2 total)

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

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

Clean up your code and make sure that it runs. I stopped trying to fix your code after the third error. Also use the insert/edit code sample when inserting code. It maintains your formatting, and makes it much easier to read.

You must specify a column name in an insert.

INSERT INTO #temp (MED_ID,QNXT_WAIVER_ATTRIBUTE,EFFDATE,TERMDATE,<you are missing a column name here>)
VALUES ('123456','ELDERLY OR DISABLED WAIVER WITH CONSUMER DIRECTION ','4/1/2017','1/7/2019','NULL','NULL');?

You cannot specify more columns than a table has. In the example above your temp table only has four columns, but you have spots for five columns. I assume that you actually want to remove the trailing comma ,.
The number of columns in the INSERT statement must match the number of values in the VALUES clause. Your INSERT statement has four (or five) columns, but you've supplied six values.
NULL values should not be quoted. 'NULL' is a string containing the word NULL, NULL is a NULL value.

You have four problems in ONE statement.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA