query assistance: Query for the first and latest wish

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.On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:

Quote:

Originally Posted by

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|||Eugene (alsu50@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 22, 1:25 pm, Utahd...@.hotmail.com wrote:

Quote:

Originally Posted by

On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hi all,

Quote:

Originally Posted by

I have the following table

Quote:

Originally Posted by

Name Date Wish Valid

Quote:

Originally Posted by

Quote:

Originally Posted by

So, a typical data set looks like:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

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!|||On Feb 22, 1:29 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Eugene (als...@.gmail.com) writes:

Quote:

Originally Posted by

So, a typical data set looks like:

Quote:

Originally Posted by

Quote:

Originally Posted by

>
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

Wow. Simple and elegant, what else can I say?! Thank you!|||On Feb 22, 1:29 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Eugene (als...@.gmail.com) writes:

Quote:

Originally Posted by

So, a typical data set looks like:

Quote:

Originally Posted by

Quote:

Originally Posted by

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|||Eugene wrote:

Quote:

Originally Posted by

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.

The 'Valid' column is redundant (you can use MAX(Date) instead) and
breakable (what if a row with Valid = 1 is deleted?). I'd ditch it
if I were you.|||On Feb 22, 6:41 pm, "Eugene" <als...@.gmail.comwrote:

Quote:

Originally Posted by

On Feb 22, 1:25 pm, Utahd...@.hotmail.com wrote:
>
>
>

Quote:

Originally Posted by

On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Hi all,

Quote:

Originally Posted by

Quote:

Originally Posted by

I have the following table

Quote:

Originally Posted by

Quote:

Originally Posted by

Name Date Wish Valid

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

So, a typical data set looks like:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

I can do UNION, but is there another way to do that?
Thank you.

Quote:

Originally Posted by

-- Put them into a temporary table:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

-- Then compare the values

Quote:

Originally Posted by

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

Good luck!

Quote:

Originally Posted by

-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.

Friday, March 23, 2012

Query for the first and latest wish

No comments:

Post a Comment

query assistance

Blog Archive

About Me