Grouped Year Ranges with breaks in Years

[wbochar]

I'm working with a lot of year ranges and in the end they need to be "StartYear - EndYear".

So the following would data..

Sandra 1972
Sandra 1973
Sandra 1974
Sandra 1975
Sandra 1976
Johnny 2000
Johnny 2001
Johnny 2002
Johnny 2003

would in the end be:

Sandra 1972-1976
Johnny 2000-2003

I've got this kind of thing working no problem using SQL, grouping and min/max.. but when this happens:

Sandra 1972
Sandra 1973
Sandra 1975
Sandra 1976
Sandra 1977

Grouping them together and getting the max and the min year would be wrong as there is no 1974.

it should output:

Sandra 1972-1973
Sandra 1975-1977

Whats the smartest way of handling this? order the data by year subtract the lowest from the highest year and compare the result with the rowcount?

records returned: 5
Sandra 1972
Sandra 1973
Sandra 1975
Sandra 1976
Sandra 1977
Highest year : 1977
Lowest year : 1972

I'm tempted to program something in C# to handle this, but was wondering if there was a SQL way to deal with it?

wbochar

 

[vongrunt]

My favourite question these days:

SQL2000 or 2005?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

OK, SQL2000 then.

This is sample data:

create table blah (firstname varchar(20), Yr smallint)
insert into blah values ('Sandra', 1972)
insert into blah values ('Sandra', 1973)
insert into blah values ('Sandra', 1975)
insert into blah values ('Sandra', 1976)
insert into blah values ('Sandra', 1977)
insert into blah values ('Johnny', 2000)
insert into blah values ('Johnny', 2001)
insert into blah values ('Johnny', 2002)
insert into blah values ('Johnny', 2003)

This returns starting years:

select A.firstName, A.Yr
from blah A
left outer join blah B on A.firstName = B.firstName and A.Yr = B.Yr+1
where B.firstName is null

Replace + with minus and... this returns ending years:

select A.firstName, A.Yr
from blah A
left outer join blah B on A.firstName = B.firstName and A.Yr = B.Yr-1
where B.firstName is null

Finally, join these two sets on firstName and find next greatest year from second set:

select X.firstName, X.Yr as StartYear, min(Y.Yr) as EndYear
from
(	<1st query here>
) X
inner join
(	select A.firstName, A.Yr
	<2nd query here>
) Y
on X.firstName = Y.firstName and X.Yr <= Y.Yr
group by X.firstName, X.Yr 
--order by X.firstName, X.Yr

Oh btw. I almost forgot

drop table blah

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]