Extract latest price for different material number

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

General SQL Server Forums

New to SQL Server Programming

Extract latest price for different material number

Author

Topic

arnesbeganovic
Starting Member

9 Posts

Posted - 2010-10-11 : 03:00:59

Hi
I work with material economics and I get purchase orders from our customers. One PO has date, few material numbers, type of orders, date and so on. All PO’s goes in one table with columns, and one below other. I have till example purchase order G111 and it has 3 materials with a date 2010-10-09. After few days came another one G222 with 5 materials, but 2 of them are same. It looks like here in my table:
PO Material Date Type Price Currency
G111 111111 2010-10-09 AAAA 56 USD
G111 222222 2010-10-09 AAAA 13 USD
G111 333333 2010-10-09 AAAA 52 USD
G222 444444 2010-10-13 AAAA 100 USD
G222 111111 2010-10-13 AAAA 60 USD
G222 222222 2010-10-13 AAAA 20 USD
G222 555555 2010-10-13 AAAA 20 USD
G222 666666 2010-10-13 AAAA 20 USD
Now, I need to extract all materials with latest prices and all information which goes with it.
111111 60 2010-10-13 AAAA USD
222222 20 2010-10-13 AAAA USD
333333 52 2010-10-09 AAAA USD
444444 100 2010-10-13 AAAA USD
555555 20 2010-10-13 AAAA USD
666666 20 2010-10-13 AAAA USD

Note that material 333333 is from PO G111 and all others are from PO G222. I have tried with:

SELECT Material, max(Date) From TableName Group by Material

And a get:
111111 2010-10-13
222222 2010-10-13
333333 2010-10-09
444444 2010-10-13
555555 2010-10-13
666666 2010-10-13

So, I get right material and date but I cannot add column Price or Type or Currency in it. I am guessing that I need to have more complicated code but I am not so good with SQL. Can you help me?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-10-11 : 03:22:47

Try this

Select tbl.Material,tbl.max_date,Type, Price, Currency from (
SELECT Material, max(Date) as max_date From DatabaseName Group by Material) tbl
inner join <table_name> a on a. Material=tbl.Material and a. Date =tbl.max_date

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

arnesbeganovic
Starting Member

9 Posts

Posted - 2010-10-11 : 06:41:05

It works. Thanks :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-10-11 : 07:18:36

quote:
Originally posted by arnesbeganovic

It works. Thanks :)

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources