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 12:45 pm

    #1978616

    mw112009 - Monday, February 5, 2018 12:01 PM

    Managed to find the solution a similar solution in "STACK OVER FLOW" and then had to modify it slightly. So it works .. Here you go 


    ;WITH T AS
    (
    SELECT *,
       DENSE_RANK() OVER (ORDER BY EligMonth) - EligMonth AS Grp
    FROM #t
    ),
    CONSECUTIVE_MONTHS_TOGETHER as
    (
    SELECT
         BeneficiaryID,
         MIN(EligMonth) AS RangeStart,
       MAX(EligMonth) AS RangeEnd,
         MAX(EligMonth) - MIN(EligMonth) as DIFF
    FROM T
    GROUP BY BeneficiaryID, 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 > 2 -- OR ( >= (3) )
    */

    Here is the internet article that helped me....

    https://stackoverflow.com/questions/7608370/how-can-i-check-a-group-of-numbers-are-consecutive-in-t-sql

    CASE CLOSED ... NO FURTHER HELP NEEDED. Have a wonderful day .

    THIS DOES NOT WORK.  You haven't bothered to understand what this is doing, and you are not validating your results that you do get.  For instance, with this amended data, there is no gap, but this so-called solution is saying that there is a gap of eleven months

    INSERT INTO #t(BeneficiaryID, EligYear, EligMonth )
    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,1 UNION
    Select '0068576102',2017,2 UNION
    Select '0068576102',2017,3 UNION
    Select '0068571234',2017,4 UNION
    Select '0068571234',2017,5

    The reason that this code doesn't work is that it depends on numbers that are sequential, but you're IGNORING THE YEAR, which means that the month numbers are cyclical, not sequential.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA