skywills skywills - 7 months ago 19
SQL Question

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

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

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;