Posted by Steve Kass on 03/04/07 18:22

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

[Back to original message]