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)