Trying to group data by adjacent years

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to group data by adjacent years

Author

Topic

sumo
Starting Member

45 Posts

Posted - 2003-10-21 : 14:05:54

I have the following sample data:

1995 VOLKSWAGEN GOLF
1996 VOLKSWAGEN GOLF
1999 VOLKSWAGEN GOLF
2000 VOLKSWAGEN GOLF
2001 VOLKSWAGEN GOLF
2003 VOLKSWAGEN GOLF
1995 HONDA      CIVIC
1996 HONDA      CIVIC
1999 HONDA      CIVIC
2000 HONDA      CIVIC

I am having trouble figuring out how to query this data to group it like the following:

1995-1996 VOLKSWAGEN GOLF
1999-2001 VOLKSWAGEN GOLF
2003-2003 VOLKSWAGEN GOLF
1995-1996 HONDA      CIVIC
1999-2000 HONDA      CIVIC

The idea is to group the data by adjacent years and display the range of years in the grouping. I can't see a way to do this without looping row-by-row on the data with a cursor or an outside program (i.e. VB). Anyone have any ideas?

Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-21 : 16:14:52

select
cast(min(Y) as char(4)) + ' - ' + cast(max(Y) as char(4)) as YearSpan,
Car
from
(select Car, Y,
Y-(select count(distinct Y) from t tt where tt.Car=t.Car and tt.Y<t.Y) yy
from t) z
group by Car, yy

Seems this is one of those very very rare cases when it is worth
comparing against a cursor solution's performance.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-21 : 16:15:49

Create a table with the years you want to query and join to that

create table #a (period char(11), y int)
insert #a select '1995-1996', 1995
insert #a select '1995-1996', 1996
insert #a select '1997-1998', 1997
...

from tbl
join #a
on tbl.yr = #a.y
group by #a.period

You then just need to think about how to populate #a.

or just

select convert(char(4),((yr+1)/2)+1) + ' - ' + convert(char(4),((yr+1)/2)+2)
...
from tbl
group by (yr+1)/2

or something like that

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources