The best way to do this computational logic in T-SQL

Question

My program must pass the amount to the request to perform this calculation, but in my case it cycles through line by line and subtracts the correct amount, I know that this is not an efficient way to implement it. therefore, I am here to find a better way.

PS: This is just my project code, I regret that for some reason I can not publish the full source code. Now I have restructured 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 should be appreciated. thanks

Answer 1

Version 1: I 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) The third solution (SQ2000 +) uses a triangular connection :

 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

I am sure that this query will not work in any case, since "index" is a keyword and therefore should be enclosed in square brackets to indicate otherwise.

In general, it is not recommended to do anything in stages for performance.

If I read it correctly, you set each balance column to the sum column minus the variable @total_value_to_deduct or set it to 0 if the deductions result in a negative amount. If this is true, then why not just do the calculations on it directly? Without any expected results, I cannot double check my logic, but please correct me if I am wrong and it is more difficult than that.

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

Edit: Ok, thanks for editing the question, now it is more clear. You are trying to take the total amount for all accounts in sequence. I will see if I can come up with a script for this and edit my answer further.

Edit # 2: Well, I couldn't find a way to do this without going through all the lines (I tried a recursive CTE, but couldn't get it to work), so I did it with a while loop, as before. It effectively makes 3 data accesses in each row, although I tried to push this down to 2, but again no luck. I send it anyway if it will be faster than yours now. This should be all the necessary code (except for creating / filling the table).

 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 3

Here is one way to do it. He finds the first current amount greater than or equal to the requested amount, and then updates all the records involved in this amount. This should probably be written differently in the sense that you need to enter the "toDeduct" column and initially have a sum value. This will allow this update to work on previously used datasets, because toDeduct = 0 means that nothing can be subtracted from this row. Additionally, an index on toDeduct, idx will allow you to quickly filter the Deduct <> 0, which you would use to reduce the number of pointless 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 at your cursor. You could improve performance by simply declaring a fast_forward cursor:

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

And you can rewrite the fetch cycle to avoid repeating the fetch instruction:

 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 

Helps to slightly change the part of the cursor.

Answer 4

If your indexes don't have spaces, the easiest solution would be

• Create a recursive CTE , starting with the value, to subtract and reduce it in the recursive part.
• Use CTE Results to Update 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_NUMBER , you can fix the problem with a space, but this complicates the request 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 

Exit

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

Answer 5

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