Posted by Eugene on 02/23/07 01:41

On Feb 22, 1:25 pm, Utahd...@hotmail.com wrote:
> 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

Utah,

Thank you for the idea! However, having the extra step of getting the
temp table is not something that I think the DBA here would approve.
The good news is that the date field is the datetime (defaulting to
getdate()) and it puts the date and time up to milliseconds, so the
chances for two people making the wish at the same time are very
minimal.

Thanks again!

[Back to original message]