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)