Mr Drew: Agreed! Thanks for validating.
I did modify the query ( Also added more test data, Added 2 more users )
BTW - I am looking for at least 3 consecutive months in any given year.
Yes, this works when the numbers are sequential ( the difference between 2 consecutive numbers is one )
If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t( BeneficiaryID VARCHAR(10), EligYear INT, EligMonth INT );
INSERT INTO #t(BeneficiaryID, EligYear, EligMonth )
Select '0068576102',2016,3 UNION
Select '0068576102',2016,6 UNION
Select '0068576102',2016,7 UNION
Select '0068576102',2016,8 UNION
Select '0068576102',2016,9 UNION
Select '0068576102',2016,10 UNION
Select '0068576102',2016,11 UNION
Select '0068576102',2016,12 UNION
Select '0068576102',2017,10 UNION
Select '0068576102',2017,11 UNION
Select '0068576102',2017,12 UNION
Select '0078576103',2016,8 UNION
Select '0078576103',2016,9 UNION
Select '78576103',2016,8 UNION
Select '78576103',2016,9 UNION
Select '78576103',2016,10
;
WITH T AS
(
SELECT *,
DENSE_RANK() OVER (ORDER BY EligMonth) - EligMonth AS Grp
FROM #t
)
,
CONSECUTIVE_MONTHS_TOGETHER as
(
SELECT
BeneficiaryID,
EligYear,
MIN(EligMonth) AS RangeStart,
MAX(EligMonth) AS RangeEnd,
MAX(EligMonth) - MIN(EligMonth) as DIFF
FROM T
GROUP BY BeneficiaryID,EligYear, Grp
)
Select * FROM CONSECUTIVE_MONTHS_TOGETHER WHERE DIFF >= 2 --( See Explanation )
/*
This solution works when the numbers are in a sequence ( that is the case with my example )
So if you had any 3 months of consecutive coverage the difference between the largest and smallest must be > 1 -- OR ( >= (2) )
*/