question

tee avatar image
tee asked

Carry forward latest value with distinct columns criteria

Hi

Please advice me on writing a stored procedures or update statement when there's some distinct , order by & group by condition?

In my case, I would need to carry forward latest month's data as current month dataset. Yet, my dataset has various products type & entity group.

Eg. The table designed as below. is the value where it needs to get from BMC110, 1/1/2009 with the quantity of 100.

EntityID ProductID Period Quantity EN1 BMC110 1/1/2009 100 EN1 BMC110 1/2/2009 EN1 BML120 1/1/2009 300 EN2 CMN120 1/3/2010 50 EN2 CPN120 1/3/2010 120

With this Select query,I'm able to retrieve for one record.

SELECT TOP 1 (Product.Quantity) from Product WHERE ProductID = 'BMC110' AND EntityID ='EN1' ORDER BY Period DESC                    

Could you please advise and show me the SQL on handling this problem?

Thank you!

sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.