Query for the first and latest wish

[Eugene]

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.

[Utahduck]

On Feb 22, 3:09 pm, "Eugene" <[e-mail]:

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

[Erland Sommarskog]

Eugene (al****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Eugene]

On Feb 22, 1:25 pm, [e-mail] wrote:

On Feb 22, 3:09 pm, "Eugene" <[e-mail]:

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!

[Eugene]

On Feb 22, 1:29 pm, Erland Sommarskog <[e-mail]:

Eugene ([e-mail]) 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, [e-mail]

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!

[Eugene]

On Feb 22, 1:29 pm, Erland Sommarskog <[e-mail]:

Eugene ([e-mail]) 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, [e-mail]

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

[Ed Murphy]

Eugene wrote:

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.

[Utahduck]

On Feb 22, 6:41 pm, "Eugene" <[e-mail]:

On Feb 22, 1:25 pm, [e-mail] wrote:

On Feb 22, 3:09 pm, "Eugene" <[e-mail]:

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.