Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Find Maximum Consecutive Years In a Series Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, March 12, 2003 9:55 AM
 Old Hand Group: General Forum Members Last Login: Yesterday @ 6:42 AM Points: 345, Visits: 285
 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 Year1 19931 19941 19951 20011 2002The 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
Post #10547
 Posted Thursday, March 13, 2003 6:01 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:42 AM Points: 6,610, Visits: 5,894
 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 #aupdate 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 NULLupdate 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 NULLselect 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.
Post #56084
 Posted Thursday, March 13, 2003 8:54 AM
 Old Hand Group: General Forum Members Last Login: Yesterday @ 6:42 AM Points: 345, Visits: 285
 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
Post #56085
 Posted Monday, April 15, 2013 9:00 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, April 19, 2013 12:05 PM Points: 1, Visits: 12
 I am fairly new to sql...I am trying to understand--in updating the flags, why xid is null condition is used
Post #1442347
 Posted Friday, April 19, 2013 9:17 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:42 AM Points: 6,610, Visits: 5,894
 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 IDThis 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.
Post #1444477
 Posted Friday, April 19, 2013 10:02 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 10:54 AM Points: 22,092, Visits: 29,017
 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)gowith 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, cntfrom 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;godrop table #a;go`
Post #1444499

 Permissions