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;

Answer 2

If your indexes don't have gaps, the simplest solution would be to

• Create a recursive CTE, starting with the value to deduct and decrementing it in the recursive part.
• Use the results of the CTE to update your actual table

SQL Statement

;WITH q AS (
  SELECT  idx, amount, balance, 130 AS Deduct
  FROM    tbl1
  WHERE   idx = 1
  UNION ALL
  SELECT  t.idx, t.amount, t.balance, q.Deduct - q.balance
  FROM    q
          INNER JOIN @tbl1 t ON t.idx = q.idx + 1  
  WHERE   q.Deduct - q.balance > 0 
)
UPDATE  @tbl1
SET     Balance = CASE WHEN q.Balance - q.Deduct > 0 THEN q.Balance - q.Deduct ELSE 0 END
FROM    q
        INNER JOIN tbl1 t ON t.idx = q.idx   

---

Using ROW_NUMBERyou can alleviate the gap problem but it complicates the query a bit.

;WITH r AS (
  SELECT  idx, amount, balance, rn = ROW_NUMBER() OVER (ORDER BY idx)
  FROM    tbl1
), q AS (
  SELECT  rn, amount, balance, 130 AS Deduct, idx
  FROM    r
  WHERE   rn = 1
  UNION ALL
  SELECT  r.rn, r.amount, r.balance, q.Deduct - q.balance, r.idx
  FROM    q
          INNER JOIN r ON r.rn = q.rn + 1  
  WHERE   q.Deduct - q.balance > 0 
)
UPDATE  tbl1
SET     Balance = CASE WHEN q.Balance - q.Deduct > 0 THEN q.Balance - q.Deduct ELSE 0 END
FROM    q
        INNER JOIN @tbl1 t ON t.idx = q.idx

Test script

DECLARE @tbl1 TABLE (idx INTEGER, Amount INTEGER, Balance INTEGER)
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)

;WITH q AS (
  SELECT  idx, amount, balance, 130 AS Deduct
  FROM    @tbl1
  WHERE   idx = 1
  UNION ALL
  SELECT  t.idx, t.amount, t.balance, q.Deduct - q.balance
  FROM    q
          INNER JOIN @tbl1 t ON t.idx = q.idx + 1  
  WHERE   q.Deduct - q.balance > 0 
)
UPDATE  @tbl1
SET     Balance = CASE WHEN q.Balance - q.Deduct > 0 THEN q.Balance - q.Deduct ELSE 0 END
FROM    q
        INNER JOIN @tbl1 t ON t.idx = q.idx

SELECT  *
FROM    @tbl1

Output

idx Amount  Balance
1   50      0
2   30      0
3   20      0
4   50      20
5   60      60

Answer 3

I'm pretty sure this query won't work anyway, as "index" is a keyword and so should be wrapped in square brackets to indicate otherwise.

In general it's not a good idea to do anything on a row-by-row basis for performance.

If I'm reading it right, you're setting each balance column to the amount column minus the @total_value_to_deduct variable, or setting it to 0 if the deductions would result in a negative amount. If that's true then why not just do calculations on that directly? Without you posting any expected results I can't double check my logic, but please correct me if I'm wrong and it's more complicated than this.

UPDATE tbl1
SET    balance = CASE
                   WHEN amount < @total_value_to_deduct THEN 0
                   ELSE amount - @total_value_to_deduct
                 END

Edit: OK thanks for the edit to the question it's more clear now. You're trying to take the total amount over all the accounts sequentially. I'll see if I can come up with a script to do this and edit my answer further.

Edit #2: OK, I couldn't find a way of doing it without interating through all of the rows (I tried a recursive CTE, but couldn't get it to work) so I've done it with a while loop like you did originally. It's effectively doing 3 data accesses per row though - I tried to knock this down to 2 but again no luck. I'm posting it anyway in case it's faster than what you have now. This should be all the code you need (apart from the table create/populate).

DECLARE @id INT
SELECT @id = Min([index])
FROM   tbl1

WHILE @id IS NOT NULL
  BEGIN
      UPDATE tbl1
      SET    balance = CASE
                         WHEN amount < @total_value_to_deduct THEN 0
                         ELSE amount - @total_value_to_deduct
                       END
      FROM   tbl1
      WHERE  [index] = @id

      SELECT @total_value_to_deduct = CASE
                                        WHEN @total_value_to_deduct < amount THEN 0
                                        ELSE @total_value_to_deduct - amount
                                      END
      FROM   tbl1
      WHERE  [index] = @id

      SELECT @id = Min([index])
      FROM   tbl1
      WHERE  [index] > @id
 END

Answer 4

Here is one of the ways to do it. It finds first running sum greater than or equal to requested amount and then updates all records participating in this sum. This should probably be written differently in a sense that a column "toDeduct" should be introduced and would initially have the value of amount. This would allow this update to work over previously used data sets, because toDeduct = 0 would mean that nothing can be deducted from this row. Furthermore, an index on toDeduct, idx will allow for quick toDeduct <> 0 filter you would use to lessen number of meaningless searches/updates.

declare @total_value_to_deduct int
set @total_value_to_deduct = 130

update tbl1
set balance = case when balance.idx = tbl1.idx 
           then balance.sumamount - @total_value_to_deduct
               else 0
         end
from tbl1 inner join
(
    select top 1 *
    from
    (
          select idx, (select sum (a.amount) 
                 from tbl1 a 
                where a.idx <= tbl1.idx) sumAmount
          from tbl1
    ) balance
      where balance.sumamount >= @total_value_to_deduct
      order by sumamount
) balance
  on tbl1.idx <= balance.idx

Now on to your cursor. One would gain performance by simply declaring cursor fast_forward:

declare csDeduct Cursor local fast_forward
    for select idx, balance 
          from tbl1 
         where balance > 0 
         order by idx

And you might rewrite fetch loop to avoid repeating fetch statement:

open csDeduct 
while 1 = 1
begin
   fetch next from csDeduct into @cs_index, @cs_balance
   if @@fetch_status <> 0
      break

    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

end
close csDeduct 
deallocate csDeduct 

Makes changing select part of a cursor a bit easier.

Answer 5

Create a new column in the table with the previous balance for each row, then you could use a trigger on INSERT/UPDATE to create the balance for the newly inserted row.