Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

finding gaps between termination date and next enrollment date and filter by gap >= 45 days for year 2013 Expand / Collapse
Author
Message
Posted Thursday, July 10, 2014 11:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 19, Visits: 124
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 2 AND 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
Post #1591266
Posted Thursday, July 10, 2014 12:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 1,920, Visits: 19,370
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
Post #1591283
Posted Thursday, July 10, 2014 1:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 19, Visits: 124
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:

universalmemberid memberid enrollmentdate terminationdate
7777JANE 1234 2011-12-19 00:00:00.000 2012-10-31 00:00:00.000
7777JANE 3245 2014-01-01 00:00:00.000 2199-12-31 00:00:00.000
8888MARK 1212 2013-10-01 00:00:00.000 2013-12-31 00:00:00.000
8888MARK 1212 2014-01-01 00:00:00.000 2199-12-31 00:00:00.000
10000JANE 5647 2013-08-01 00:00:00.000 2013-09-11 00:00:00.000
10000JANE 9870 2013-09-12 00:00:00.000 2013-12-31 00:00:00.000
22222MIKE 1215 2013-02-01 00:00:00.000 2013-08-31 00:00:00.000
22222MIKE 3098 2013-09-01 00:00:00.000 2013-12-31 00:00:00.000
55555MARTHA 5671 2011-09-01 00:00:00.000 2012-04-08 00:00:00.000
55555MARTHA 5671 2012-04-09 00:00:00.000 2013-01-31 00:00:00.000
55555MARTHA 5671 2013-02-01 00:00:00.000 2013-05-01 00:00:00.000
55555MARTHA 5671 2013-06-02 00:00:00.000 2199-12-31 00:00:00.000
66666JANE 9033 2011-10-01 00:00:00.000 2012-04-30 00:00:00.000
66666JANE 9033 2012-05-01 00:00:00.000 2013-01-31 00:00:00.000
66666JANE 9033 2013-02-01 00:00:00.000 2013-12-31 00:00:00.000
77777MARY 0912 2012-03-01 00:00:00.000 2012-08-31 00:00:00.000
77777MARY 0912 2012-09-01 00:00:00.000 2199-12-31 00:00:00.000
88888ANTHONY 5467 2011-08-01 00:00:00.000 2013-05-31 00:00:00.000
88888ANTHONY 5467 2013-10-01 00:00:00.000 2199-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
Post #1591299
Posted Thursday, July 10, 2014 1:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 1,920, Visits: 19,370
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
Post #1591305
Posted Thursday, July 10, 2014 1:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 19, Visits: 124
Correct, J.

Thanks a lot!
Post #1591308
Posted Thursday, July 10, 2014 2:25 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 155, Visits: 586
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




"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1591322
Posted Thursday, July 10, 2014 3:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 19, Visits: 124
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
Post #1591340
Posted Thursday, July 10, 2014 3:35 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 155, Visits: 586
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




"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1591349
Posted Thursday, July 10, 2014 3:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 19, Visits: 124
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
Post #1591351
Posted Thursday, July 10, 2014 3:58 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 155, Visits: 586
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.



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1591354
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse