Count of consecutive years of participation (islands and gaps)

I have included a snippet of my dataset. Unfortunately, I would need to try and find the specific things I have tried as I blew them away when they did not work. If you would like me to do that I can.

Here is the type of thing we would like to see. Notice how we can just copy and paste this to start working on an answer.

with Something as

(

select 1 as ID, cast('2000-05-03' as datetime) as MyDate union all

select 1, '2001-06-10' union all

select 1, '2002-04-02' union all

select 1, '2005-07-29' union all

select 1, '2010-12-15' union all

select 4, '2001-05-07' union all

select 4, '1999-08-01' union all

select 4, '2000-07-05' union all

select 4, '2001-08-01' union all

select 9, '2002-05-01' union all

select 9, '2000-04-02'

)

What I don't understand is how you came up with the results you state you want. What is the business logic for determining if it is a consecutive year? If the subsequent entry is made within a year of the previous one? Assuming that is the case your desired output does not seem to match the data.

This part of your question scares me:

Cursors are generally very slow and for inserting data it is almost never the best way to do that. Why do you need a cursor? It sounds like perhaps a case expression would be much better. We can help with that part too if you give us some details.

DDL & consumable test data is the important thing. Something more akin to the following...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp;

CREATE TABLE #temp (

ID INT NOT NULL,

[Date] DATE NOT NULL

);

INSERT #temp (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');

SELECT * FROM #temp t

Based on your desired output, I think the following is what you're looking for...

WITH AddRN AS (

SELECT

t.ID,

t.Date,

ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.Date) AS RN

FROM

#temp t

)

SELECT

a.ID,

SUM(CASE WHEN DATEDIFF(yy, b.Date, a.Date) = 1 THEN 1 ELSE 0 END) AS ConsecYears

FROM

AddRN a

LEFT JOIN AddRN b

ON a.ID = b.ID

AND a.RN = b.RN + 1

GROUP BY

a.ID

the results...

ID ConsecYears

--------- -----------

1 2

4 2

9 0

Edit... Note that the above solution works in the earlier versions of SQL Server. SQL Server 2012 introduced the LAG & LEAD functions which would allow you to get the same results in a single query and without having to do a self join.

I assume that "from #temp t" is due to you using a temp table to run the cte query and that it would not be a part of a final solution?

Not really. It's only in TempDB because for him, it's not a permanent part of any database. you would put it in your database and not in TempDB.

The reason people here do that is so that they don't bloat their databases with a bunch of tables they will only use once (to answer a question).

Yep... Exactly that... I put my test data in a temp table called #temp, so that's what I queried. 😉

I have to agree with Sean, the desired output does not match the data, specifically ID 4 surely has 3 or maybe even 4) consecutive years, unless you have a third column which determines the order?

How do you classify consecutive years?