SQL Subtract Variable From Rows

Question

Is there a way to subtract a variable and use the remainder of that variable for the next column in MS-SQL

Basically what i want to do is subtract a certain value and start playing of the values from var1-3 in order

Before:

ID|var1|var2|var3
 1| 500| 100| 200
 2| 800| 400| 300
 3|1200|3200|2400

Sample 1 - Subtract 600 I would get

ID|var1|var2|var3
 1|   0|   0| 200
 2| 800| 400| 300
 3|1200|3200|2400

Sample 2 - Subtract 750 I would get

ID|var1|var2|var3
 1|   0|   0|  50
 2| 800| 400| 300
 3|1200|3200|2400

Sample 3 - Subtract 900 I would get

ID|var1|var2|var3
 1|   0|   0|   0
 2| 700| 400| 300
 3|1200|3200|2400

The field with '0' can be 'null'.

This is what i have tried but is very bulky :|

DECLARE @Amount DECIMAL(18,2) = 900

CREATE TABLE #table(ID [int] NULL, var1 decimal(18,2),  var2 decimal(18,2), var3 decimal(18,2))
INSERT INTO #table (ID,var1,var2,var3) VALUES (1,500,100,200)
INSERT INTO #table (ID,var1,var2,var3) VALUES (2,800,400,300)
INSERT INTO #table (ID,var1,var2,var3) VALUES (3,1200,3200,2400)
DECLARE @CurrentRow INT
DECLARE @OrgAmount DECIMAL(18,2)

WHILE (SELECT SUM( ISNULL(var1,0)) + SUM( ISNULL(var2,0)) + SUM( ISNULL(var3,0)) FROM #table) != 0 AND @Amount != 0
BEGIN
    SELECT TOP 1 @CurrentRow = ID FROM #table
    WHERE var1 != 0 OR var2 != 0 OR  var3 != 0
    ORDER BY ID ASC
    
    UPDATE #table
    SET var1 = CASE WHEN var1 - @Amount < 0 THEN 0 ELSE var1 - @Amount END,
    @OrgAmount = var1
    WHERE @CurrentRow = ID
    SET @Amount = CASE WHEN @Amount - @OrgAmount < 0 THEN 0 ELSE @Amount - @OrgAmount END
    
    
    UPDATE #table
    SET var2 = CASE WHEN var2 - @Amount < 0 THEN 0 ELSE var2 - @Amount END,
    @OrgAmount = var2
    WHERE @CurrentRow = ID
    SET @Amount = CASE WHEN @Amount - @OrgAmount < 0 THEN 0 ELSE @Amount - @OrgAmount END

    UPDATE #table
    SET var3 = CASE WHEN var3 - @Amount < 0 THEN 0 ELSE var3 - @Amount END,
    @OrgAmount = var3
    WHERE @CurrentRow = ID
    SET @Amount = CASE WHEN @Amount - @OrgAmount < 0 THEN 0 ELSE @Amount - @OrgAmount END

END

SELECT * FROM #table

DROP TABLE #table

Thank you for the assist.

Answer 1

While I agree with the comments, that this probably could be better laid out, the following sql shows one approach to the problem:

declare @subtract int
set @subtract = 900

;WITH CTE_Data as (
    select id=1, var1= 500, var2 = 100, var3 = 200
    union select id=2, var1= 800, var2 = 400, var3 = 300
    union select id=3, var1= 1200, var2 = 3200, var3 = 2400
),
CTE_RunningTotals as (
    select 
        id, 
        pretot = 0, 
        var1, var2, var3,       
        tot1 = var1, 
        tot2 = var1 + var2, 
        tot3 = var1 + var2 + var3
    from CTE_Data where id = 1  
    union all
    select 
        d.id, 
        pretot = rt.tot3,
        d.var1, d.var2, d.var3,     
        tot1 = d.var1 + rt.tot3,
        tot2 = d.var1 + d.var2 + rt.tot3,
        tot3 = d.var1 + d.var2 + d.var3 + rt.tot3
    from
        CTE_Data d
        join CTE_RunningTotals rt on d.id = rt.id+1
    where d.id > 1
)
select 
    id,
    var1 = case when pretot - @subtract >= 0 then var1 
            else case when tot1 - @subtract > 0 then tot1 - @subtract else 0 end
            end,
    var2 = case when tot1 - @subtract >= 0 then var2
            else case when tot2 - @subtract > 0 then tot2 - @subtract else 0 end
            end,
    var3 = case when tot2 - @subtract >= 0 then var3 
            else case when tot3 - @subtract >0 then tot3 - @subtract else 0 end
            end
from CTE_RunningTotals

returns

id    var1    var2    var3
1     0       0       0
2     700     400     300
3     1200    3200    2400

CTE_Data is simply the input data, you can change this for your source table.

CTE_RunningTotals contains the raw data, plus a running total of all the values in the table upto the current row/column.

If the previous row/column is zero (ie the subtract value is greater than the running total so far) the subtract value is taken from the current value, otherwise the data value is returned.

You might find it easier to work out what is going on if you select * from CTE_RunningTotals after the CTE declarations so that you can see the working.