Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Help me with the SELECT statement please ? RE: Help me with the SELECT statement please ?
February 5, 2018 at 1:10 pm
mw112009 - Monday, February 5, 2018 1:07 PMMr 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) )
*/
Please ignore this.. I will post another modified query.. I did notice another error...