Posted by Utahduck on 02/23/07 21:44

On Feb 22, 6:41 pm, "Eugene" <als...@gmail.com> wrote:
> 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!

Oops, yeah, temporary tables have their places and this wouldn't be
one of them. But, I think you've got the idea.

[Back to original message]