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?