;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 diffFROM CTE1INNER JOIN CTE2 ON CTE1.universalmemberid = CTE2.universalmemberid AND CTE1.RN + 1 = CTE2.RNWHERE DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) >= 45

;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 diffFROM CTE1INNER JOIN CTE2 ON CTE1.universalmemberid = CTE2.universalmemberid AND CTE1.RN + 1 = CTE2.RNWHERE DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) >= 45

;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 diffFROM CTE1INNER JOIN CTE1 AS CTE2 ON CTE1.universalmemberid = CTE2.universalmemberid AND CTE1.RN + 1 = CTE2.RNWHERE DATEDIFF(day, CTE1.terminationdate, CTE2.enrollmentdate) >= 45

;WITH CTE1 AS ( SELECT RN = ROW_NUMBER() OVER (PARTITION BY universalmemberid ORDER BY enrollmentdate), * FROM #nj_members )SELECT * FROM CTE1 ORDER BY universalmemberid, enrollmentdate

;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

;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 diffFROM CTE1INNER JOIN CTE1 AS CTE2 ON CTE1.universalmemberid = CTE2.universalmemberid AND CTE1.RN + 1 = CTE2.RN