# Find Maximum Consecutive Years In a Series

• I am trying to solve a problem of how to find the maximum count of consecutive years in a series of records. In the following example:

ID Year

1 1993

1 1994

1 1995

1 2001

1 2002

The value would be 3 for the years 1993 to 1995. The best method I figured out so far is to loop through each group of years for each ID and do a running count. Does anyone have any suggestions on other ways of doing this?

Thanks,

Anton Ochss

• create table #a (ID int,Year int)

insert into #a values(1,1993)

insert into #a values(1,1994)

insert into #a values(1,1995)

insert into #a values(1,2001)

insert into #a values(1,2002)

insert into #a values(2,1995)

insert into #a values(2,1996)

insert into #a values(2,2000)

insert into #a values(2,2001)

insert into #a values(2,2002)

insert into #a values(2,2003)

create table #b (ID int,Year int,Flag int)

insert into #b select ID,Year,0 from #a

update b set Flag=1 from #b b left outer join #b x on x.ID = b.ID and x.Year = (b.Year - 1) where x.ID IS NULL

update b set Flag=2 from #b b left outer join #b x on x.ID = b.ID and x.Year = (b.Year + 1) where x.ID IS NULL

select b.ID,max((x.Year - b.Year) + 1) as 'ct' from #b b inner join #b x on x.ID = b.ID and x.Year = (select min(y.Year) from #b y where y.ID = b.ID and y.Year > b.Year and y.Flag=2) where b.Flag = 1 group by b.ID

Far away is close at hand in the images of elsewhere.
Anon.

• David,

This is great....I guess I need to brush up on my queries when dealing with sets of data.

Thanks a bunch. This will be a big help on our project.

Anton

• I am fairly new to sql...I am trying to understand--in updating the flags, why xid is null condition is used

• The is null condtions is used to set the flag to 1 if no prececeding year is present, 2 if there is no following year otherwise it is left at zero to indicate the presence of both preceding and following years.

If you select the contents of #b you will see how the values are set.

The next query then counts the difference between the years where flag is 1 and 2 and find the max number grouped by the ID

This query was written a long time ago in SQL2000 and I would not write it like this nowadays on later versions of SQL Server.

A better solution could be found and many of the experts on this forum would know a good starting point.

I do not know Oracle so I would not know where to start.

Far away is close at hand in the images of elsewhere.
Anon.

• Here is another option, don't know how it will perform or scale:

`create table #a (ID int,CalYear int)`

`insert into #a values(1,1993)`

`insert into #a values(1,1994)`

`insert into #a values(1,1995)`

`insert into #a values(1,2001)`

`insert into #a values(1,2002)`

`insert into #a values(2,1995)`

`insert into #a values(2,1996)`

`insert into #a values(2,2000)`

`insert into #a values(2,2001)`

`insert into #a values(2,2002)`

`insert into #a values(2,2003)`

`go`

`with base1 as (`

`select`

` ID,`

` CalYear,`

` rn = CalYear - row_number() over (partition by ID order by CalYear asc)`

`from`

` #a`

`), base2 as (`

`select`

` ID,`

` CalYear,`

` rn,`

` cnt`

`from`

` base1 b1`

` cross apply (select cnt = count(*) from base1 b2 where b1.ID = b2.ID and b1.rn = b2.rn)ds(cnt)`

`)`

`select ID, max(cnt) as MaxCnt from base2 group by ID;`

`go`

`drop table #a;`

`go`

Viewing 6 posts - 1 through 5 (of 5 total)