How to subtract N cumulatively from multiple rows

See more:

T-SQL

Hi,
I'm wondering if there're a better way how to subtract certain value from multiple rows in a table. Let me explain the problem using this simplified example:

We want to track stock quantites of different commodities we bought in a table that looks like this:

Id  ReceivedOn  CommodityId  OriginalQty  RemainingQty  UnitCostPrice
---------------------------------------------------------------------
 1  2015-02-01          123           10             8            $35
 2  2015-01-24          123           40            70            $28
 3  2015-01-14           80           25            25           $120
 4  2015-01-18           80           10            10           $125         

Now the task is to withdraw 72 pieces of the commodity #123 from the warehouse and return the total cost price of the removed items. We want to subtract from the oldest rows first. So, in this case the required behavior would be:

1. To set the RemainingQty on row #2 to 0.
2. To set the RemainingQty on row #1 to 6 (because there were two more pieces that we couldn't subtract from row #2).
3. To return (70 * $28) + (2 * $35) as the total cost price of withdrawed commodities.

I've got it done easily using imperative approach and cursors but of course, it's not how T-SQL procedures should be written in general. That makes me wonder if there's a better way how to do it.

Thanks.