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.