The best way to perform such calculation logic in T-SQL

Question

My program require to pass in an amount into the query to perform such calculation, but in my case it loop through row by row and deduct the correct amount, i know this is not a efficient way to implement. so i am here to seeking a better way.

PS: It is just my draft code, i am sorry about i cannot post the complete source code for some reason. Now i had re-structure my code to make it more complete and reasonable.

   --- the amount column is just for reference.

    insert into tbl1 (idx,amount,balance) values (1, 50, 50)
    insert into tbl1 (idx,amount,balance) values (2, 30, 30)
    insert into tbl1 (idx,amount,balance) values (3, 20, 20)
    insert into tbl1 (idx,amount,balance) values (4, 50, 50)
    insert into tbl1 (idx,amount,balance) values (5, 60, 60)


declare @total_value_to_deduct int
declare @cs_index int, @cs_balance int, @deduct_amount int

set @total_value_to_deduct = 130

declare csDeduct Cursor for select idx, balance from tbl1 where balance > 0 
open csDeduct fetch next from csDeduct into @cs_index, @cs_balance

while @@FETCH_STATUS = 0 and @total_value_to_deduct > 0
begin

   if @cs_balance >= @total_value_to_deduct  
    set @deduct_amount = @total_value_to_deduct
   else
    set @deduct_amount = @cs_balance

    -- contine deduct row by row if the total_value_to_deduct is not 0
    set @total_value_to_deduct = @total_value_to_deduct - @deduct_amount

    update tbl1 set balance = balance - @deduct_amount  where idx = @cs_index
    fetch next from csDeduct into @cs_index, @cs_balance
end

close csDeduct
deallocate csDeduct

Expected Result :

idx          amount          balance
1              50               0
2              30               0
3              20               0
4              50              20
5              60              60

Your help is must appreciate. thank

Answer 1

Revision 1: I have added a third solution

1. First solution (SQL2005+; online query)

   DECLARE @tbl1 TABLE ( idx INT IDENTITY(2,2) PRIMARY KEY, amount INT NOT NULL, balance INT NOT NULL );

   INSERT INTO @tbl1 (amount,balance) VALUES (50, 50); INSERT INTO @tbl1 (amount,balance) VALUES (30, 30); INSERT INTO @tbl1 (amount,balance) VALUES (20, 20); INSERT INTO @tbl1 (amount,balance) VALUES (50, 50); INSERT INTO @tbl1 (amount,balance) VALUES (60, 60);

   DECLARE @total_value_to_deduct INT; SET @total_value_to_deduct = 130;

   WITH CteRowNumber AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY idx) AS RowNum FROM @tbl1 a ), CteRecursive AS ( SELECT a.idx, a.amount, a.amount AS running_total, CASE WHEN a.amount <= @total_value_to_deduct THEN 0 ELSE a.amount - @total_value_to_deduct END AS new_balance, a.RowNum FROM CteRowNumber a WHERE a.RowNum = 1 --AND a.amount < @total_value_to_deduct UNION ALL SELECT crt.idx, crt.amount, crt.amount + prev.running_total AS running_total, CASE WHEN crt.amount + prev.running_total <= @total_value_to_deduct THEN 0 WHEN prev.running_total < @total_value_to_deduct AND crt.amount + prev.running_total > @total_value_to_deduct THEN crt.amount + prev.running_total - @total_value_to_deduct ELSE crt.amount END AS new_balance, crt.RowNum FROM CteRowNumber crt INNER JOIN CteRecursive prev ON crt.RowNum = prev.RowNum + 1 --WHERE prev.running_total < @total_value_to_deduct ) UPDATE @tbl1 SET balance = b.new_balance FROM @tbl1 a

2. Second solution (SQL2012)

   UPDATE @tbl1 SET balance = b.new_balance FROM @tbl1 a INNER JOIN ( SELECT x.idx, SUM(x.amount) OVER(ORDER BY x.idx) AS running_total, CASE WHEN SUM(x.amount) OVER(ORDER BY x.idx) <= @total_value_to_deduct THEN 0 WHEN SUM(x.amount) OVER(ORDER BY x.idx) - x.amount < @total_value_to_deduct --prev_running_total < @total_value_to_deduct AND SUM(x.amount) OVER(ORDER BY x.idx) > @total_value_to_deduct THEN SUM(x.amount) OVER(ORDER BY x.idx) - @total_value_to_deduct ELSE x.amount END AS new_balance FROM @tbl1 x ) b ON a.idx = b.idx;

3. Third solution (SQ2000+) uses triangular join:

   UPDATE @tbl1 SET balance = d.new_balance FROM @tbl1 e INNER JOIN ( SELECT c.idx, CASE WHEN c.running_total <= @total_value_to_deduct THEN 0 WHEN c.running_total - c.amount < @total_value_to_deduct --prev_running_total < @total_value_to_deduct AND c.running_total > @total_value_to_deduct THEN c.running_total - @total_value_to_deduct ELSE c.amount END AS new_balance FROM ( SELECT a.idx, a.amount, (SELECT SUM(b.amount) FROM @tbl1 b WHERE b.idx <= a.idx) AS running_total FROM @tbl1 a ) c )d ON d.idx = e.idx;