March 12, 2003 at 9:55 am
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
March 13, 2003 at 6:01 am
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.
March 13, 2003 at 8:54 am
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
April 15, 2013 at 9:00 am
I am fairly new to sql...I am trying to understand--in updating the flags, why xid is null condition is used
April 19, 2013 at 9:17 am
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.
April 19, 2013 at 10:02 am
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)
You must be logged in to reply to this topic. Login to reply