Finding the LAST value - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Finding the LAST value

Author

Topic

masteripper
Starting Member

25 Posts

Posted - 2010-04-19 : 05:07:00

Well hello to everybody...
I have a rather difficult situation and i need some help
I have a table with products prices
Every month a product is calculated its cost price but every year we have inventory and this changes all the cost prices to zero and again when the product is manufactured again it gets its new cost price.
Let me show an example

ProductCode....Product_Cost....YearCalc....MonthCalc
100............200................2009........11.....
100............200................2009........12.....
100............ 0 ................2010........1.....
100............ 0 ................2010........2.....
100............ 180...............2010........3.....
200.............170...............2009........11
200.............150...............2009........12
200............. 0 ...............2010........1 ....
200............. 0 ...............2010........2 ....
200............. 0 ...............2010........3 ....
300............. 0 ...............2010........3......

So i would like this result
ProductCode.....LastCostPrice
100..............180
200..............150
300.............. 0

Some explanation
100 ... is a old product and on March was manufactured and its costprice is calculated on March 2010
200 ... is a old product and after Inventory was not manufactured so its last costprice was calculated back in 2009
300 Is a brand new product so it has no "history") and because is not manufactured yet its costprice is 0...the code is allowed to get into the "system" in order to be ready for the costing process (rare case but it happens)
Well can anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 05:17:02

[code]
SELECT ProductCode,Product_Cost
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY CASE WHEN Product_Cost>0 THEN 1 ELSE 0 END DESC,YearCalc DESC,MonthCalc DESC
FROM Table
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-04-19 : 05:17:36

select * from
(select row_number()over(partition by productcode order by cast(monthcalc as varchar(32))+'/01/'+cast(yearcalc as varchar(32))desc)as rid ,* from table where Product_Cost <> 0)s
where rid = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 05:27:13

quote:
Originally posted by bklr

select * from
(select row_number()over(partition by productcode order by cast(monthcalc as varchar(32))+'/01/'+cast(yearcalc as varchar(32))desc)as rid ,* from table where Product_Cost <> 0)s
where rid = 1

How do you think this will return last row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masteripper
Starting Member

25 Posts

Posted - 2010-04-19 : 05:29:23

Well ...what can i say....you are MASTERS...thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 05:36:36

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources