Home Forums SQL Server 7,2000 T-SQL Find Maximum Consecutive Years In a Series RE: Find Maximum Consecutive Years In a Series

  • 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