|
|
Posted by Eugene on 02/23/07 02:10
On Feb 22, 1:29 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Eugene (als...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Hm, I thought this was like conversation mode, so the reply would have
posted right underneath the answer. Anyway, Thanks a bunch, Erland!
BTW, for the folks who is looking at this some time later, the working
query looks like the following:
SELECT a.Name, a.FirstDate, b.Wish as FirstWish,
a.LastDate, c.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
Navigation:
[Reply to this message]
|