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