OLSONEJ (6/29/2015)
I have a data set (snippet below) and I need to count the number of consecutive years based on a date in time for each ID as represented below.ID DATE
------ --------
1 2000-05-03
1 2001-06-10
1 2002-04-02
1 2005-07-29
1 2010-12-15
4 2001-05-07
4 1999-08-01
4 2000-07-05
4 2001-08-01
9 2002-05-01
9 2000-04-02
My result set needs to be something like:
ID Count of Consecutive Years
------- -----------------------------
1 2
4 2
9 0
I know this is a gaps and islands type problem but nothing I have been able to find is working once I attempt modification so that it can fit my dataset. Any help or pointers would be appreciated. Please note that I am going to use the data return to populate another table that is currently being populated using a cursor that utilizes an insert statement based on different codes.
I'm sure that the cursor could probably be replaced, but to answer what I think you asked, try this:
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'),
(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, YEAR(D.[Date]) AS THE_YEAR
FROM @INPUT_DATA AS D
)
SELECT T1.ID, ISNULL(NULLIF(COUNT(DISTINCT T2.THE_YEAR), 0) + 1, 0) AS CONSECUTIVE_YEARS
FROM TRANSFORMED AS T1
LEFT OUTER JOIN TRANSFORMED AS T2
ON T1.ID = T2.ID
AND T1.THE_YEAR = T2.THE_YEAR - 1
GROUP BY T1.ID
Let us know what works, and if we can help eliminate the cursor.
EDIT: Corrected the code when I saw your last post about ID 4 should have 3, ... if that's true, however, so should ID 1, unless I don't understand the rules for consecutive years. THAT really needs to be spelled out in exact detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)