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/1/2015)


    The solution presented works to a point. An additional point of criteria, that I was only informed of this morning, is that we only want to count as consecutive those entries that are consecutive through the current fiscal year. Meaning that if there is a gap throughout the years where they were previously consecutive and then lapsed that those previously consecutive years would not be counted.

    Okay, how about this query instead (it doesn't have good sample data, so no rows are returned - you can create better sample data):

    DECLARE @THE_DATE AS date = GETDATE();

    DECLARE @CURRENT_YEAR AS int = CASE WHEN MONTH(@THE_DATE) < 7 THEN YEAR(@THE_DATE) - 1 ELSE YEAR(@THE_DATE) END;

    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, '2006-10-05'),

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

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

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

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

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

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

    WITH TRANSFORMED AS (

    SELECT D.ID, CASE WHEN MONTH(D.[Date]) < 7 THEN YEAR(D.[Date]) - 1 ELSE YEAR(D.[Date]) END AS FISCAL_YEAR

    FROM @INPUT_DATA AS D

    )

    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

    GROUP BY T1.ID

    HAVING MAX(T2.FISCAL_YEAR) = @CURRENT_YEAR

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