Okay, I see where the problem was. I thought I had a lock on seeing where a gap was, but alas, it was faulty. I had to come up with a better method, so I decided to look at ALL YEARS in the span of all the data, and take the maximum year that doesn't appear in the data, and require all selected years to be larger than that value, or greater than the minimum year minus 1 if there are no gaps.

Here's the updated code:

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

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

DECLARE @MIN_YEAR AS int, @MAX_YEAR AS int;

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-03-31'),

(1, '2000-04-24'),

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

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

(1, '2001-03-09'),

(1, '2010-05-11'),

(1, '2011-03-25'),

(1, '2012-11-25'),

(1, '2013-06-18'),

(1, '2014-02-16'),

(1, '2015-05-17'),

/*(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 @MIN_YEAR = MIN(FISCAL_YEAR), @MAX_YEAR = MAX(FISCAL_YEAR)

FROM @TRANSFORMED;

SELECT *

FROM @TRANSFORMED

ORDER BY ID, FISCAL_YEAR;

WITH YEARS AS (

SELECT @MIN_YEAR AS FISCAL_YEAR

UNION ALL

SELECT FISCAL_YEAR + 1

FROM YEARS

WHERE FISCAL_YEAR + 1 <= @MAX_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.FISCAL_YEAR >

ISNULL(

(

SELECT MAX(Y.FISCAL_YEAR)

FROM YEARS AS Y

LEFT OUTER JOIN @TRANSFORMED AS T3

ON Y.FISCAL_YEAR = T3.FISCAL_YEAR

AND T3.ID = T1.ID

WHERE T3.ID IS NULL

),

(

SELECT MIN(T4.FISCAL_YEAR)

FROM @TRANSFORMED AS T4

WHERE T4.ID = T1.ID

) - 1)

GROUP BY T1.ID

HAVING MAX(T2.FISCAL_YEAR) = @CURRENT_YEAR;