Grouping numbers

[Chris]

I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable??? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

PLEASE HELP

[Erland Sommarskog]

Chris (ch*********@gmail.com) writes:

I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable??? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges appears
to make things a lot more complicated.

However, the query you posted has syntax which is not legal in SQL Server,
but has a touch of Access, a product of which I have no experience.

Could you clarify which product and which version of that product you
are using? If you are using Access, I recommend that you try an Access
newsgroup instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

What you posted implies a serious design error about history tables.
What you are askimg for is a violaiton of 1NF and the principle that
display is done in the front end and never the backend in a tiered
architecture. And finally the syntax you posted is not valid.

Want to try again?

[Steve Kass]

Erland Sommarskog wrote:

>

I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges appears
to make things a lot more complicated.

Here is a recursive solution that will do the job when
MAXRECURSION is no greater than the number of separate
years for one individual player. Some of the complication
is to get around limitations in what a recursive query
can contain (no GROUP BY, for example). The idea is
slippery, but not quite as messy as it looks.

CREATE TABLE T (
Pid INT,
yr INT,
primary key (Pid,yr)
)
go

INSERT T (Pid,yr) VALUES(1,1)
INSERT T (Pid,yr) VALUES(1,4)
INSERT T (Pid,yr) VALUES(1,3)
INSERT T (Pid,yr) VALUES(1,5)
INSERT T (Pid,yr) VALUES(1,6)
INSERT T (Pid,yr) VALUES(1,9)
INSERT T (Pid,yr) VALUES(1,10)
INSERT T (Pid,yr) VALUES(2,29)
INSERT T (Pid,yr) VALUES(2,30)
INSERT T (Pid,yr) VALUES(2,31)
INSERT T (Pid,yr) VALUES(2,9)
INSERT T (Pid,yr) VALUES(2,130)
INSERT T (Pid,yr) VALUES(2,131)
INSERT T (Pid,yr) VALUES(2,132)
go

with Mins(iter,Pid,lastwrite,lastfound,rowYr,yrs) as (
select
0,
Pid,
min(yr),
min(yr),
min(yr),
cast(min(yr) as varchar(max))
from T
group by Pid
union all
select
Mins.iter+1,
Mins.Pid,
case when min(T.yr) over (partition by Mins.Pid) = Mins.lastfound + 1
--and Mins.rightest < Mins.upto
then Mins.lastwrite else min(T.yr) over (partition by Mins.Pid) end,
min(T.yr) over (partition by Mins.Pid),
T.yr,
Mins.yrs
+ case when min(T.yr) over (partition by Mins.Pid) Mins.lastfound + 1
then case when Mins.lastfound Mins.lastwrite
then rtrim(Mins.lastfound) else '' end
+ ',' + rtrim(min(T.yr) over (partition by Mins.Pid))
else case when Mins.lastfound = Mins.lastwrite
then '-' else '' end
end
from Mins join T
on Mins.Pid = T.Pid
and Mins.lastfound < T.yr
and Mins.rowYr = Mins.lastfound
), AllSteps(Pid,yrs,lastwrite,lastfound,rk) as (
select distinct Pid, yrs,lastwrite,lastfound,
rank() over (partition by Pid order by iter desc)
from Mins
)
select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else ','+rtrim(lastfound) end
from AllSteps
where rk = 1

go

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 95508D54-0B01-431B-8B58-880146787216

[Steve Kass]

Correction: The final SELECT should be

select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else '' end
from AllSteps
where rk = 1

The version I posted lists the last year twice, if it is not
part of a preceding range of years.

SK

Steve Kass wrote:

Erland Sommarskog wrote:

>
>
I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges

appears

to make things a lot more complicated.

Here is a recursive solution that will do the job when
MAXRECURSION is no greater than the number of separate
years for one individual player. Some of the complication
is to get around limitations in what a recursive query
can contain (no GROUP BY, for example). The idea is
slippery, but not quite as messy as it looks.

<snip>

select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else
','+rtrim(lastfound) end
from AllSteps
where rk = 1

go

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 95508D54-0B01-431B-8B58-880146787216

[--CELKO--]

Here is a guess at what you should have used for DDL if you had fgiven
us specs.

CREATE TABLE PlayerHistory
(player_id INTEGER NOT NULL
REFERENCES Pleyers(player_id)
team_name CHAR(15) NOT NULL
REFERENCES Teams(team_name),
start_year INTEGER NOT NULL
CHECK(start_year BETWEEN 1950 AND 9999),
end_year INTEGER
CHECK(start_year BETWEEN 1950 AND 9999),
CHECK(start_year <= end_year),
PRIMARY KEY ((player_id , team_name ,start_year)
);

A null end_year means the player is still with that team. You use a
VIEW with WHERE end_year IS NULL to get the current situation; you do
not put it in a separate table. What you seem to have is a table in
which an attribute (temproal duration) is split over several rows.

See how simple basic RDBMS design can save you from complex kludges?
Here is a guess at what you should have used for DDL if you had fgiven
us specs.

CREATE TABLE PlayerHistory
(player_id INTEGER NOT NULL
REFERENCES Pleyers(player_id)
team_name CHAR(15) NOT NULL
REFERENCES Teams(team_name),
start_year INTEGER NOT NULL
CHECK(start_year BETWEEN 1950 AND 9999),
end_year INTEGER
CHECK(start_year BETWEEN 1950 AND 9999),
CHECK(start_year <= end_year),
PRIMARY KEY ((player_id , team_name ,start_year)
);

A null end_year means the player is still with that team. You use a
VIEW with WHERE end_year IS NULL to get the current situation; you do
not put it in a separate table. What you seem to have is a table in
which an attribute (temproal duration) is split over several rows.

See how simple basic RDBMS design can save you from complex kludges?