Home Forums SQL Server 2008 T-SQL (SS2K8) Count of consecutive years of participation (islands and gaps) RE: Count of consecutive years of participation (islands and gaps)

  • OLSONEJ (7/6/2015)


    sg, my apologies for the confusion.

    This will operate based on fiscal year 07-01-2014 to 06-30-2015. I am in need of a count of consecutive fiscal years if the person has participation in this fiscal year and has previous participation in preceding fiscal years up until a lapse or the beginning of their participation. The query I included was for calendar year as my thought was to start basic and expand as I went along since I have never had recourse to do a count like this before.

    Okay, I'm assuming your stated range is FISCAL 2015. This is a gap problem, and my previous query would not account for gaps. Also, FISCAL 2015 ended just short of a week ago, so I adjusted the date for the overall query to go back into June so that I wouldn't need to adjust the sample data. Please test this one and let me know:

    EDIT: Updated the code to handle the possiblility that there are 0 records without an adjacency, and added the sample data for ID value 5 to test it.

    DECLARE @THE_DATE AS date = DATEADD(dd, -10, GETDATE());

    DECLARE @CURRENT_YEAR AS int = CASE WHEN MONTH(@THE_DATE) > 6 THEN YEAR(@THE_DATE) + 1 ELSE YEAR(@THE_DATE) END;

    SELECT @CURRENT_YEAR AS CURRENT_YEAR

    DECLARE @INPUT_DATA AS TABLE (

    ID INT NOT NULL,

    [Date] DATE NOT NULL

    );

    INSERT INTO @INPUT_DATA (ID, [Date]) VALUES

    (1, '2000-05-03'),

    (1, '2001-06-10'),

    (1, '2002-04-02'),

    (1, '2005-07-29'),

    (1, '2010-12-15'),

    (1, '2015-04-15'),

    (1, '2014-06-15'),

    (1, '2013-05-12'),

    (4, '2001-05-07'),

    (4, '1999-08-01'),

    (4, '2000-07-05'),

    (4, '2001-08-01'),

    (5, '2013-06-01'),

    (5, '2014-06-01'),

    (5, '2015-06-01'),

    (9, '2002-05-01'),

    (9, '2000-04-02');

    DECLARE @TRANSFORMED AS TABLE (

    ID INT,

    FISCAL_YEAR INT,

    RN INT

    );

    INSERT INTO @TRANSFORMED (ID, FISCAL_YEAR, RN)

    SELECT D.ID, CASE WHEN MONTH(D.[Date]) > 6 THEN YEAR(D.[Date]) + 1 ELSE YEAR(D.[Date]) END AS FISCAL_YEAR,

    DENSE_RANK() OVER(PARTITION BY D.ID ORDER BY CASE WHEN MONTH(D.[Date]) > 6 THEN YEAR(D.[Date]) + 1 ELSE YEAR(D.[Date]) END) AS RN

    FROM @INPUT_DATA AS D;

    SELECT *

    FROM @TRANSFORMED

    ORDER BY ID, FISCAL_YEAR;

    SELECT T1.ID, ISNULL(NULLIF(COUNT(DISTINCT T2.FISCAL_YEAR), 0) + 1, 0) AS CONSECUTIVE_YEARS

    FROM @TRANSFORMED AS T1

    LEFT OUTER JOIN @TRANSFORMED AS T2

    ON T1.ID = T2.ID

    AND T1.FISCAL_YEAR = T2.FISCAL_YEAR - 1

    AND T1.RN >

    ISNULL(

    (SELECT MAX(T3.RN)

    FROM @TRANSFORMED AS T3

    WHERE T3.ID = T1.ID

    AND T3.FISCAL_YEAR NOT IN

    (

    SELECT T4.FISCAL_YEAR

    FROM @TRANSFORMED AS T4

    INNER JOIN @TRANSFORMED AS T5

    ON T4.ID = T5.ID

    AND T5.FISCAL_YEAR IN (T4.FISCAL_YEAR - 1, T4.FISCAL_YEAR + 1)

    )

    ), 0)

    GROUP BY T1.ID

    HAVING MAX(T2.FISCAL_YEAR) = @CURRENT_YEAR

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)