Find Maximum Consecutive Years In a Series

  • aochss

    SSCommitted

    Points: 1677

    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

  • David Burrows

    SSC Guru

    Points: 64592

    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.

  • aochss

    SSCommitted

    Points: 1677

    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

  • avg998877

    Grasshopper

    Points: 17

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

  • David Burrows

    SSC Guru

    Points: 64592

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442180

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply