Click here to Skip to main content
15,434,087 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

Updated 1-Feb-15 10:08am
Zoltán Zörgő 1-Feb-15 16:48pm    
I don't think there is a declarative way, even less because you will need transactions and locks to do it properly. It is nothing wrong with cursors.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900