Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 View to only show latest date

Author  Topic 

GordonCopestake
Starting Member

8 Posts
Posted - 2012-03-16 : 12:54:16
Hi all,
I have a table CustomerPrices that lists all the prices of items and the dates the prices were set. The current price of an item is the latest date for the group of items. I would like to make a view that ONLY shows the current prices for each item.

e.g.
itemID | price | DateSet
apple | $0.50 | 01/01/01
orange | $1.00 | 02/03/02
apple | $0.55 | 01/03/01
apple | $0.67 | 05/07/02
pear | $3.20 | 05/03/05
orange | $2.00 | 06/03/06

desired output from view
itemID | Price | DateSet
apple | $0.67 | 05/07/02
orange | $2.00 | 06/03/06
pear | $3.20 | 05/03/05

Could someone point me in the right direction? I'm thinking I am having to play with groupings and MAX of DateSet but I can't seem to get my head around it.

Thanks in advance

Gordon

X002548
Not Just a Number

15586 Posts
Posted - 2012-03-16 : 13:00:53
OK cupcake

SELECT * FROM yourTable o WHERE EXISTS (
SELECT * FROM yourTable i WHERE i.itemID = o.itemID
GROUP BY itemID
HAVING o.DateSet = MAX(i.DateSet)
)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

GordonCopestake
Starting Member

8 Posts
Posted - 2012-03-16 : 13:08:37
Brett, many thanks for your swift reply!

This works beautifully in a query but not in a view :-(

Is there a way to get the same functionality in a view?
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-03-16 : 13:30:35
What do you mean?

CREATE VIEW myView99
AS
SELECT * FROM yourTable o WHERE EXISTS (
SELECT * FROM yourTable i WHERE i.itemID = o.itemID
GROUP BY itemID
HAVING o.DateSet = MAX(i.DateSet)
)
GO

SELECT * FROM myView99
GO

POST YOUR Code so we can see. It's hard to work in the dark
Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

GordonCopestake
Starting Member

8 Posts
Posted - 2012-03-16 : 13:50:33
Ignore me i'm being a dumbass!

Works fine thanks!

I was trying to execute it in the view design pane in management studio and it was giving errors, but it works fine from a query window which is all that matters.

Thanks Brett
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-03-16 : 14:06:40
NO..DO NOT USE SSMS AS A GUI TOOL

For the most part, ONLY USE THE QUERY WINDOW for code

You will find almost ALL people here write code. And IF you need to do something (like create a linked server, create a login, user, whatever)...use the script button to se what SSMS does, then save it and never do that again


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site