Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Maximum Consecutive Years In a Series Expand / Collapse
Author
Message
Posted Wednesday, March 12, 2003 9:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:55 AM
Points: 354, Visits: 361
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






Post #10547
Posted Thursday, March 13, 2003 6:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 6,917, Visits: 6,990
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.

Post #56084
Posted Thursday, March 13, 2003 8:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:55 AM
Points: 354, Visits: 361
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 6,917, Visits: 6,990
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.

Post #1444477
Posted Friday, April 19, 2013 10:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse