good morning autoexcrement!
ok, I have tested the latest one you posted:
;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 #nj_members
WHERE universalmemberid NOT IN
(
SELECT
CTE1.universalmemberid
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
)
and is giving me false positives on the live data like the following examples:
universalmemberid enrollmentdate terminationdate
1998JOHNNY2010-10-01 00:00:00.0002011-06-30 00:00:00.000
1998JOHNNY2011-07-01 00:00:00.0002012-06-30 00:00:00.000
1998JOHNNY2012-07-01 00:00:00.0002012-07-31 00:00:00.000
1998JOHNNY2012-09-01 00:00:00.0002013-12-31 00:00:00.000
0824RICHARD2010-11-01 00:00:00.0002011-02-28 00:00:00.000
0824RICHARD2012-04-01 00:00:00.0002013-11-11 00:00:00.000
0824RICHARD2013-11-12 00:00:00.0002013-12-31 00:00:00.000
Okay, I modified your query by removing the = sign, because I'm looking for gap higher than 45 days.
The member 1998JOHNNY has no gap bigger than 45 days. The member 0824RICHARD
has correctly a gap bigger than 45 days where termination date is '2011-02-28' and the next enrollment date is '2012-04-01', but since it is outside of the desired year 2013.
Shouldn't that be excluded?
Thanks a lot, autoexcrement! 😎
Tammy