Posted by Erland Sommarskog on 02/22/07 22:29

Eugene (alsu50@gmail.com) writes:
> So, a typical data set looks like:
>
> Name Date Wish Valid
> Joe 02/01/2007 Ice Cream 0
> Joe 02/04/2007 Bicycle 0
> Joe 02/06/2007 PS3 0
> Joe 02/22/2007 XBox 360 1
> Mary 02/02/2007 Barbie 0
> Mary 02/04/2007 Cindy 0
> Mary 02/06/2007 Barbie house 0
> Mary 02/20/2007 Get married 1
>
> My users want to see the initial wish at some point and another one
> some time later (they provide dates). So, if someone wanted to see
> changes in wishes between 02/03 and till 02/15, they would get that
> Joe's initial wish was Bicycle and the latest that he wanted was PS3.
> As for Mary, she started wanting Cindy and ended up thinking about the
> Barbie house.

SELECT a.Name, a.FirstDate, f.Wish .FirstWish,
a.LastDate, l.Wish as LastWish
FROM (SELECT Name, FirstDate = MIN(Date), LastDate = MAX(Date)
FROM wishes
WHERE Date BETWEEN @start AND @end
GROUP BY Name) AS a
LEFT JOIN wishes b ON a.Name = b.Name AND a.FirstDate = b.date
LEFT JOIN wishes c ON a.Name = c.Name AND a.LastDate = c.date


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]