Posted by Utahduck on 02/22/07 22:25

On Feb 22, 3:09 pm, "Eugene" <als...@gmail.com> wrote:
> Hi all,
>
> I have the following table
>
> Name Date Wish Valid
>
> Name is person's name, date defaults to getdate() and is never
> assigned directly (datetime field), Wish is some message, and Valid is
> bit, 1 indicates if the wish is the latest, and therefore valid. All
> previous wishes are kept in database, and are "invalidated" by setting
> the Valid to 0.
>
> 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.
>
> I can do UNION, but is there another way to do that?
> Thank you.

-- Put them into a temporary table:

SELECT Name, Min(Date) as FirstWishDate, Max(Date) as LastWishDate
INTO #FirstAndLast
FROM Wishlist
WHERE Date >= @StartingDate
AND Date <= @EndingDate

-- Then compare the values

SELECT t.Name, t.FirstWishDate, w1.Wish as FirstWish, t.LastWishDate,
w2.Wish as LastWish
FROM #FirstAndLast t,
WishList w1,
WishList w2
WHERE t.Name = w1.Name
AND t.FirstWishDate = w1.Date
AND t.Name = w2.Name
AND t.FirstWishDate = w2.Date

Of course, this is supposing they've only made one wish per day,
otherwise you'll duplicate some rows. If that is the case, make sure
you are tracking times as well.

Good luck!

-Utah

[Back to original message]