July 10, 2014 at 11:49 am
Hello experts!
I need your help.
I would like to calculate the date gap between the first termination date and the next enrollment date. This would be done for all the records. I cannot have more than 1 gap of 45 days or less to count these members as continuously enrolled. To clarify the gap only applies to the measurement year i.e. any time between 1/1/2013 and 12/31/2013.
This has to be done for the measurement year which in my case is 2013 (1/1/20113 to 12/31/2013). The problem is that the members can change providers and this triggers a disenrollment and reenrollment which can happen any time of the year.
Here are the possible records I can get:
UNIVERSALMEMBERID ENROLLMENTDATE TERMINATIONDATE
7777 12/19/2011 10/31/2012
7777 1/1/2014 12/31/2199
8888 10/1/2013 12/31/2013
8888 1/1/2014 12/31/2199
9999 10/1/2012 10/8/2012
9999 10/9/2012 12/31/2199
10000 8/1/2013 9/11/2013
10000 9/12/2013 12/31/2013
10000 1/1/2014 12/31/2199
11111 1/1/2014 12/31/2199
22222 2/1/2013 8/31/2013
22222 9/1/2013 12/31/2013
22222 1/1/2014 12/31/2199
33333 1/1/2014 12/31/2199
44444 1/1/2014 12/31/2199
55555 9/1/2011 4/8/2012
55555 4/9/2012 1/31/2013
55555 2/1/2013 5/1/2013
55555 6/2/2013 12/31/2199
66666 10/1/2011 4/30/2012
66666 5/1/2012 1/31/2013
66666 2/1/2013 12/31/2013
66666 1/1/2014 12/31/2199
77777 8/30/2011 2/29/2012
77777 3/1/2012 8/31/2012
77777 9/1/2012 12/31/2199
88888 8/1/2011 5/31/2013
88888 10/1/2013 12/31/2199
I would delete any members with enrollmentdate > '12/31/2013'.
I would then do an update on '12/31/2199' to be '12/31/2013' since these members are still enrolled.
So from the sample data above I should get the following members as continuously enrolled and if they have a gap only to be 45 days or less:
memberid
9999
22222
55555
66666
77777
I have tried diffferent solutions I found on the web, but none of them give me what I need.
Please let me know if you need any more info or if I'm not clear enough with my explanation.
This is what I have until now:
/*PULL MEMBERS WITH DENTAL BENEFIT FROM NJ MEDICAID*/
SELECT DEV.ProductID
,DEV.MemberID
,DEV.UniversalMemberID
,EnrollmentDate
,TerminationDate
,DMV.DOB
,floor(datediff(day, DMV.DOB, CONVERT(nvarchar, '20131231', 112))/(365.25)) as Age
,ProviderID
,EnrollmentCustom4 as Category
,DentalBenefit
INTO #NJ_ADV_MEDICAID_WITH_DENTAL_BENEFITS
FROM Enrollment DEV (nolock)
JOIN Member DMV (nolock)
ON DEV.MemberID = DMV.MemberID
WHERE DentalBenefit = 'Y' AND DEV.ProductID = '07'
AND ((EnrollmentDate < '12/31/2013' AND TerminationDate > '12/31/2013')
OR (EnrollmentDate < '12/31/2013' AND TerminationDate <= '12/31/2013'))
/*FROM THE NEWLY CREATED TEMP TABLE PULL ONLY THOSE MEMBERS BETWEEN AGE 2 TO 21*/
CREATE TABLE #NJ_ADV_MCD_2_21s (enrollment int identity(1,1),
ProductID varchar(50),
MemberID varchar(100),
UniversalMemberID varchar(100),
EnrollmentDate datetime,
TerminationDate datetime,
DOB datetime,
Age int,
ProviderID varchar(80),
Category varchar(50),
DentalBenefit char(1))
INSERT #NJ_ADV_MCD_2_21s
SELECT *
FROM #NJ_ADV_MEDICAID_WITH_DENTAL_BENEFITs
WHERE Age BETWEEN 2AND 21
ORDER BY UNIVERSALMEMBERID,ENROLLMENTDATE,TERMINATIONDATE,AGE
/*UPDATE DATE = '12/31/2199' WITH THE LAST DAY OF THE MEASUREMENT YEAR*/
UPDATE #NJ_ADV_MCD_2_21s
SET TerminationDate = '12/31/2013'
WHERE TerminationDate = '12/31/2199'
/*CREATE A COUNT OF THE UNIQUE UNIVERSALMEMBERID*/
SELECT UNIVERSALMEMBERID,COUNT(*) AS CNT_MBRIDS
INTO #NJ_ADV_MCD_MBRIDS_COUNTS
FROM #NJ_ADV_MCD_2_21s
GROUP BY UniversalMemberID
ORDER BY UniversalMemberID
/*JOIN THE COUNT WITH THE FULL TABLE*/
SELECT A.*,B.CNT_MBRIDS
INTO #NJ_ADV_MCD_2_21_COUNTS
FROM #NJ_ADV_MCD_2_21s A
JOIN #NJ_ADV_MCD_MBRIDS_COUNTS B
ON A.UniversalMemberID = B.UniversalMemberID
ORDER BY UniversalMemberID,EnrollmentDate,TerminationDate
/*ADD DATE SPANS TO THE FULL TABLE*/
select enrollment,PRODUCTID,MEMBERID,UniversalMemberID,EnrollmentDate,TerminationDate,DOB,Age,ProviderID,
Category,DentalBenefit,CNT_MBRIDS,'' AS Spans
into #NJ_ADV_MCD_2_21_spans
from #NJ_ADV_MCD_2_21_COUNTS
order by UniversalMemberID,EnrollmentDate,TerminationDate
/*ALTER THE SPANS FIELD TO BE INTEGER*/
ALTER TABLE #NJ_ADV_MCD_2_21_spans
ALTER COLUMN Spans INT
/*POPULATE THE SPANS FIELD WITH DATE DIFFERENCE*/
UPDATE #NJ_ADV_MCD_2_21_spans
SET Spans = DATEDIFF(DD,EnrollmentDate,TerminationDate)
/*PULL MEMBERS INTO DENOMINATOR TABLE THAT ONLY APPEAR 1 TIME AND HAVE THE LAST DAY OF THE MEASUREMENT YEAR AS TERMINATION DATE AND HAVE A 320 DAYS SPAN*/
SELECT *
INTO #NJ_ADV_MCD_2_21_DENOMINATOR
FROM #NJ_ADV_MCD_2_21_spans
WHERE CNT_MBRIDS = 1 AND TerminationDate = '12/31/2013'
AND Spans >= 320
ORDER BY UniversalMemberID,EnrollmentDate,TerminationDate
/*DELETE MEMBERS THAT WERE PULLED INTO THE DENOMINATOR TABLE FROM THE MAIN TABLE*/
DELETE FROM #NJ_ADV_MCD_2_21_spans
WHERE UniversalMemberID IN (SELECT UniversalMemberID FROM
#NJ_ADV_MCD_2_21_DENOMINATOR)
/*PULL MEMBERS INTO DENOMINATOR TABLE THAT ONLY APPEAR 1 TIME AND HAVE THE FIRST DAY OF THE MEASUREMENT YEAR AS ENROLLMENT DATE AND HAVE A 320 DAYS SPAN*/
INSERT INTO #NJ_ADV_MCD_2_21_DENOMINATOR
SELECT *
FROM #NJ_ADV_MCD_2_21_spans
WHERE CNT_MBRIDS = 1 AND EnrollmentDate = '1/1/2013'
AND Spans >= 320
ORDER BY UniversalMemberID,EnrollmentDate,TerminationDate
/*DELETE MEMBERS THAT WERE PULLED INTO THE DENOMINATOR TABLE FROM THE MAIN TABLE*/
DELETE FROM #NJ_ADV_MCD_2_21_spans
WHERE UniversalMemberID IN (SELECT UniversalMemberID FROM
#NJ_ADV_MCD_2_21_DENOMINATOR)
ALTER TABLE #NJ_ADV_MCD_2_21_spans
ADD continuous nvarchar(50)
-- update the last enrollment for each member without multiple gaps to True (nothing to compare to)
UPDATE #NJ_ADV_MCD_2_21_spans
SET continuous = 'True'
FROM #NJ_ADV_MCD_2_21_spans
JOIN (SELECT UniversalMemberID, max(enrollment) maxE FROM #NJ_ADV_MCD_2_21_spans GROUP BY UniversalMemberID) result
ON #NJ_ADV_MCD_2_21_spans.enrollment = result.maxE
LEFT JOIN (
SELECT gaps.UniversalMemberID, count(gaps.UniversalMemberID) AS gapCount FROM (
SELECT enrollkeys1.UniversalMemberID,
enrollkeys1.TerminationDate,
enrollkeys2.EnrollmentDate,
datediff(dd,enrollkeys1.TerminationDate,enrollkeys2.EnrollmentDate) AS [Disenrolled Days]
FROM #NJ_ADV_MCD_2_21_spans enrollkeys1
JOIN #NJ_ADV_MCD_2_21_spans enrollkeys2 ON enrollkeys1.UniversalMemberID = enrollkeys2.UniversalMemberID
-- make sure we're comparing the enrollment segments in order
WHERE enrollkeys2.enrollment = enrollkeys1.enrollment + 1
AND datediff(dd,enrollkeys1.TerminationDate,enrollkeys2.EnrollmentDate) > 1
) gaps
GROUP BY gaps.UniversalMemberID
) memberGaps ON memberGaps.UniversalMemberID = #NJ_ADV_MCD_2_21_spans.UniversalMemberID
WHERE memberGaps.gapCount = 1 OR memberGaps.gapCount IS NULL
/*DELETE MEMBERS THAT HAVE CONTINUOS ENROLLMENT AND THE COUNT OF UNIVERSALMEMBER ID IS 1 AND TERMINATION DATE IS LAST DATE OF MEASUREMENT YEAR AND SPANS FOR THAT YEAR IS LESS THAN 320*/
DELETE FROM #NJ_ADV_MCD_2_21_spans
WHERE continuous = 'true'
AND CNT_MBRIDS = 1
AND TerminationDate = '12/31/2013'
AND Spans < '320'
SELECT UNIVERSALMEMBERID,SUM(SPANS) AS SUMS
INTO #NJ_ADV_MCD_2_21_spans_sums
FROM #NJ_ADV_MCD_2_21_spans
WHERE CNT_MBRIDS > 1
AND CONTINUOUS = 'TRUE'
AND TERMINATIONDATE = '12/31/2013'
GROUP BY UniversalMemberID
INSERT INTO #NJ_ADV_MCD_2_21_DENOMINATOR
SELECT B.enrollment,ProductID,MemberID,A.UniversalMemberID,EnrollmentDate,TerminationDate,DOB,Age,ProviderID,Category,DentalBenefit,CNT_MBRIDS,Spans
FROM #NJ_ADV_MCD_2_21_spans_sums A
JOIN #NJ_ADV_MCD_2_21_spans B
ON A.UniversalMemberID = B.UniversalMemberID
WHERE SUMS >= 320
ORDER BY A.UniversalMemberID,EnrollmentDate,TerminationDate
DELETE FROM #NJ_ADV_MCD_2_21_spans
WHERE UniversalMemberID IN (SELECT UniversalMemberID
FROM #NJ_ADV_MCD_2_21_DENOMINATOR)
If you have a better way to do this than what I'm doing above, please also let me know!
Thank you so much in advanced!
TG
July 10, 2014 at 12:28 pm
Tammy...it will be easier to help you if you can please provide some scripts that create a table with suitable sample insert data scripts that describe you problem....along with the expected results based on your sample data......can you do this?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 10, 2014 at 1:17 pm
Hi J!
Here is the script for the create and insert of the table:
CREATE TABLE [dbo].[nj_members](
[universalmemberid] [varchar](100) not NULL,
[memberid] [varchar](52) NULL,
[enrollmentdate] datetime not NULL,
[terminationdate] datetime NULL
)
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('7777JANE','1234','12/19/2011','10/31/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('7777JANE','3245','1/1/2014', '12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('8888MARK','1212','10/1/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('8888MARK','1212','1/1/2014','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('10000JANE','5647','8/1/2013','9/11/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('10000JANE','9870','9/12/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('22222MIKE','1215','2/1/2013','8/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('22222MIKE','3098','9/1/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','9/1/2011','4/8/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','4/9/2012','1/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','2/1/2013','5/1/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','6/2/2013','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','10/1/2011','4/30/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','5/1/2012','1/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','2/1/2013','12/31/2013 ')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('77777MARY','0912','3/1/2012','8/31/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('77777MARY','0912','9/1/2012','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('88888ANTHONY','5467','8/1/2011','5/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('88888ANTHONY','5467','10/1/2013','12/31/2199')
select * from nj_members
and the result is:
universalmemberidmemberidenrollmentdateterminationdate
7777JANE12342011-12-19 00:00:00.0002012-10-31 00:00:00.000
7777JANE32452014-01-01 00:00:00.0002199-12-31 00:00:00.000
8888MARK12122013-10-01 00:00:00.0002013-12-31 00:00:00.000
8888MARK12122014-01-01 00:00:00.0002199-12-31 00:00:00.000
10000JANE56472013-08-01 00:00:00.0002013-09-11 00:00:00.000
10000JANE98702013-09-12 00:00:00.0002013-12-31 00:00:00.000
22222MIKE12152013-02-01 00:00:00.0002013-08-31 00:00:00.000
22222MIKE30982013-09-01 00:00:00.0002013-12-31 00:00:00.000
55555MARTHA56712011-09-01 00:00:00.0002012-04-08 00:00:00.000
55555MARTHA56712012-04-09 00:00:00.0002013-01-31 00:00:00.000
55555MARTHA56712013-02-01 00:00:00.0002013-05-01 00:00:00.000
55555MARTHA56712013-06-02 00:00:00.0002199-12-31 00:00:00.000
66666JANE90332011-10-01 00:00:00.0002012-04-30 00:00:00.000
66666JANE90332012-05-01 00:00:00.0002013-01-31 00:00:00.000
66666JANE90332013-02-01 00:00:00.0002013-12-31 00:00:00.000
77777MARY09122012-03-01 00:00:00.0002012-08-31 00:00:00.000
77777MARY09122012-09-01 00:00:00.0002199-12-31 00:00:00.000
88888ANTHONY54672011-08-01 00:00:00.0002013-05-31 00:00:00.000
88888ANTHONY54672013-10-01 00:00:00.0002199-12-31 00:00:00.000
The members that meet my requirements:
22222MIKE
55555MARTHA
66666JANE
77777MARY
The member 88888ANTHONY doesn't meet the requirements, because even though he is enrolled for 2013, he has a bigger than 45 days gap between 2013-05-31 00:00:00.000 and 2013-10-01 00:00:00.000.
That is the problem I need to solve, how do I check for gaps like that when I can have one record per member or multiple.
Thanks a lot in advanced.
Tammy
July 10, 2014 at 1:34 pm
thanks for script...just to confirm please....you are definitely running this in 2005 and not higher?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 10, 2014 at 1:50 pm
Correct, J.
Thanks a lot!
July 10, 2014 at 2:25 pm
I think you will be looking at doing something like this. You will need to adjust it to suit your needs, but hopefully the concept makes sense? You will need to do whatever you need to do as far as removing/modifying records outside of 2013, but here's one way you can locate people with gaps.
;WITH
CTE1 AS (SELECT RN = ROW_NUMBER() OVER (PARTITION BY universalmemberid ORDER BY enrollmentdate), * FROM #nj_members),
CTE2 AS (SELECT RN = ROW_NUMBER() OVER (PARTITION BY universalmemberid ORDER BY enrollmentdate), * FROM #nj_members)
SELECT
CTE1.*,
CTE2.enrollmentdate AS nextenrollmentdate,
DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) AS diff
FROM CTE1
INNER JOIN CTE2 ON
CTE1.universalmemberid = CTE2.universalmemberid
AND CTE1.RN + 1 = CTE2.RN
WHERE
DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) >= 45
July 10, 2014 at 3:09 pm
Hi autoexcrement!
First, I would like to say thank you for your time on this. 🙂
Unfortunately, when running it against the live data, this doesn't work as expected for the following scenario:
UNIVERSALMEMBERID ENROLLMENTDATE TERMINATIONDATE
1992ANNA 3/1/2012 4/30/2012
1992ANNA 7/1/2012 3/24/2013
1992ANNA 3/25/2013 7/31/2013
1992ANNA 8/1/2013 12/31/2013
I probably need to isolate the records are within the measurement year, 2013.
How can I go about doing that too, so that the 45 day gap is only checked during 2013 and not previously as I stated in my explanation?
Thanks a lot again!
Tammy
July 10, 2014 at 3:35 pm
Glad if I could help. I'd really rather not solve the entire puzzle for you, or you won't really learn much from this exercise. Plus I'm not as familiar with your data as you are. But maybe you want to do something like this? (Same as before except adding WHERE conditions to the CTE's.)
;WITH
CTE1 AS
(
SELECT
RN = ROW_NUMBER() OVER
(PARTITION BY universalmemberid ORDER BY enrollmentdate),
*
FROM #nj_members
WHERE
(enrollmentdate >= '1/1/2013' AND enrollmentdate < '2014-1-1')
OR
(terminationdate >= '1/1/2013' AND terminationdate < '2014-1-1')
),
CTE2 AS
(
SELECT
RN = ROW_NUMBER() OVER
(PARTITION BY universalmemberid ORDER BY enrollmentdate),
*
FROM #nj_members
WHERE
(enrollmentdate >= '1/1/2013' AND enrollmentdate < '2014-1-1')
OR
(terminationdate >= '1/1/2013' AND terminationdate < '2014-1-1')
)
SELECT
CTE1.*,
CTE2.enrollmentdate AS nextenrollmentdate,
DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) AS diff
FROM CTE1
INNER JOIN CTE2 ON
CTE1.universalmemberid = CTE2.universalmemberid
AND CTE1.RN + 1 = CTE2.RN
WHERE
DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) >= 45
July 10, 2014 at 3:44 pm
Hi autoexcrement!
Thank you again for your quick response.
Could this be done in more steps but easier to follow?
I tried reading upon what CTE and (SELECT RN = ROW_NUMBER() OVER (PARTITION BY
do, but I cannot get the jist of it 🙁
I will try and do what you suggested using the date filters.
Thanks a lot for your time 🙂
Tammy
July 10, 2014 at 3:58 pm
Actually, first of all, I should correct what I wrote, because you only need one CTE:
;WITH
CTE1 AS
(
SELECT
RN = ROW_NUMBER() OVER
(PARTITION BY universalmemberid ORDER BY enrollmentdate),
*
FROM #nj_members
WHERE
(enrollmentdate >= '1/1/2013' AND enrollmentdate < '2014-1-1')
OR
(terminationdate >= '1/1/2013' AND terminationdate < '2014-1-1')
)
SELECT
CTE1.*,
CTE2.enrollmentdate AS nextenrollmentdate,
DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) AS diff
FROM CTE1
INNER JOIN CTE1 AS CTE2 ON
CTE1.universalmemberid = CTE2.universalmemberid
AND CTE1.RN + 1 = CTE2.RN
WHERE
DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) >= 45
Your best bet for learning about this is Google. This may be a bit complicated for you, but I'll do my best to explain.
T-SQL CTE: https://www.google.com/search?q=t-sql+common+table+expression
T-SQL ROW NUMBER: https://www.google.com/search?q=t-sql+row_number
CTE is short for "Common Table Expression". Basically it is like creating a little temporary table. So our first step is to create a CTE (little temporary table) that holds all the records from your table with a startdate or enddate in 2013.
We are also adding a row number to our CTE. Basically we are assigning a sequential number to each record, starting at 1 for each employee, and ordered by their enrollmentdate. So each employee's first enrollment will be #1, their second will be #2, etc.
Now our CTE is done. We will call it CTE1.
Then we are selecting from CTE1, and joining a second copy of it (which we are calling CTE2) where the employeeid matches but the row number is +1. So we are basically going to compare each record per employee with their "next" record, if one exists. And we are looking at the date difference between the terminationdate of the current record (CTE1) and the enrollmentdate of their next record (CTE2). Those with a difference of 45 days or more are returned as results.
Does that make any sense?
So here's everything:
;WITH
CTE1 AS
(
SELECT
RN = ROW_NUMBER() OVER
(PARTITION BY universalmemberid ORDER BY enrollmentdate),
*
FROM #nj_members
)
SELECT * FROM CTE1 ORDER BY universalmemberid, enrollmentdate
Here's just records from 2013:
;WITH
CTE1 AS
(
SELECT
RN = ROW_NUMBER() OVER
(PARTITION BY universalmemberid ORDER BY enrollmentdate),
*
FROM #nj_members
WHERE
(enrollmentdate >= '1/1/2013' AND enrollmentdate < '2014-1-1')
OR
(terminationdate >= '1/1/2013' AND terminationdate < '2014-1-1')
)
SELECT * FROM CTE1 ORDER BY universalmemberid, enrollmentdate
Here's the final result without any limitation on 45 days:
;WITH
CTE1 AS
(
SELECT
RN = ROW_NUMBER() OVER
(PARTITION BY universalmemberid ORDER BY enrollmentdate),
*
FROM #nj_members
WHERE
(enrollmentdate >= '1/1/2013' AND enrollmentdate < '2014-1-1')
OR
(terminationdate >= '1/1/2013' AND terminationdate < '2014-1-1')
)
SELECT
CTE1.*,
CTE2.enrollmentdate AS nextenrollmentdate,
DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) AS diff
FROM CTE1
INNER JOIN CTE1 AS CTE2 ON
CTE1.universalmemberid = CTE2.universalmemberid
AND CTE1.RN + 1 = CTE2.RN
And you have the final, final result at the top of this post. Hope this helps to clarify what's going on.
July 10, 2014 at 5:45 pm
Hi autoexcrement!
Just to make sure I'm on the right path. This will not be a 2 or 3 step solution, right?
You saw my sample data and it is very convoluted.
I have like 20 steps until now including what you gave and still cannot see that I am doing the right thing here. 🙁
Getting a bit depressed that I cannot get this to work properly!!
Thank you so much for the explanation though, it was much clearer than what I've come across online.
Tammy
July 10, 2014 at 6:05 pm
Tammy-274861 (7/10/2014)
The member 88888ANTHONY doesn't meet the requirements, because even though he is enrolled for 2013, he has a bigger than 45 days gap between 2013-05-31 00:00:00.000 and 2013-10-01 00:00:00.000.That is the problem I need to solve, how do I check for gaps like that when I can have one record per member or multiple.
Thanks a lot in advanced.
Tammy
Tammy, I would like to clarify your requirements, because what you've shown is not exactly what I'd have expected from above. You want any person enrolled in 2013 who has never had more than a 45 day gap in their enrollment, ever, crossing over year lines but not looking past the 2013 enrollment year? That's what I understand from your data results.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 10, 2014 at 6:50 pm
Tammy, the code that I posted should work by itself for the DDL you provided (below). The rest of the code you provided (20 steps) I unfortunately don't have time to review and I don't know what all the data looks like beneath it. If you are starting with the data below, then my code (I think) will find the records with >=45 day gaps during 2013.
CREATE TABLE [dbo].[nj_members](
[universalmemberid] [varchar](100) not NULL,
[memberid] [varchar](52) NULL,
[enrollmentdate] datetime not NULL,
[terminationdate] datetime NULL
)
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('7777JANE','1234','12/19/2011','10/31/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('7777JANE','3245','1/1/2014', '12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate) VALUES ('8888MARK','1212','10/1/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('8888MARK','1212','1/1/2014','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('10000JANE','5647','8/1/2013','9/11/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('10000JANE','9870','9/12/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('22222MIKE','1215','2/1/2013','8/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('22222MIKE','3098','9/1/2013','12/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','9/1/2011','4/8/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','4/9/2012','1/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','2/1/2013','5/1/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('55555MARTHA','5671','6/2/2013','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','10/1/2011','4/30/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','5/1/2012','1/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('66666JANE','9033','2/1/2013','12/31/2013 ')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('77777MARY','0912','3/1/2012','8/31/2012')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('77777MARY','0912','9/1/2012','12/31/2199')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('88888ANTHONY','5467','8/1/2011','5/31/2013')
INSERT INTO nj_members (universalmemberid,memberid,enrollmentdate,terminationdate)
VALUES ('88888ANTHONY','5467','10/1/2013','12/31/2199')
July 10, 2014 at 7:47 pm
Hi Craig!
Acually I need to apply the 45 days gap ONLY for the members that have enrollment throughout 2013. e.g.
See the member below, I don't care the gap between the first termination date and the second enrollment date since it is not during 2013, but I do care for the gap between second termination date and third enrollment date and since it is less than 45 days this member would be considered enrolled continuously for 2013.
universalmemberid enrollmentdate terminationdate
1234JANE 1/1/2010 5/30/2012
1234JANE 8/1/2012 2/1/2013
1234JANE 3/1/2013 12/31/2013
Thank you for reading my issue, Craig!
Tammy
July 10, 2014 at 7:49 pm
Hi autoexcrement!
If I use the code you provided to get rid of the ones that have more than 45 days gap from my temp table for 2013. Can I also use the same but in the reverse to find the ones that actually have < 45 days?
Would that be a fair statement?
Thanks a lot!! 😀
Tammy
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply