Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


finding gaps between termination date and next enrollment date and filter by gap >= 45 days for...


finding gaps between termination date and next enrollment date and filter by gap >= 45 days for year 2013

Author
Message
Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
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
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
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

Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
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
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
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

Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
Correct, J.

Thanks a lot!
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
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
Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
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
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
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
Tammy-274861
Tammy-274861
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 177
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
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search