How to reduce the value of a column in a row with?

Question

I have a table with two columns:

No   Value
1    20
2    10
3    50
4    35
5    17

I also have a variable or parameter where the variables will reduce the value of a column in a row.

So, if my variable V = 5 then my column will update:

No   Value
1    15
2    10
3    50
4    35
5    17

Or if V = 50 then:

No   Value
1    0
2    0
3    30
4    35
5    17

How can I do that?

Answer 1

DECLARE @qty int

SET @qty= 50


WHILE @qty> 0
BEGIN

SELECT @qty= @qty- value
FROM table
WHERE no = (SELECT MIN(no) FROM table WHERE value > 0)

IF @qty< 0
    BEGIN
     UPDATE table
     SET value = ABS(@qty)
     WHERE (SELECT MIN(no) FROM table WHERE value > 0)
    END
ELSE
    BEGIN
     UPDATE table
     SET value = 0
     WHERE (SELECT MIN(no) FROM table WHERE value > 0)
    END
END

Answer 2

First prepare the structure and the data:

CREATE TABLE TAB
(
  [No] int identity(1,1) primary key,
  [Value] int
);

INSERT INTO TAB VALUES (20);
INSERT INTO TAB VALUES (10);
INSERT INTO TAB VALUES (50);
INSERT INTO TAB VALUES (35);
INSERT INTO TAB VALUES (17);

So now we define your variable to reduce the [Value]:

DECLARE @var int
SET @var = 5

And now you can query your table:

SELECT [No], CASE WHEN [Value] - @var < 0 THEN 0 ELSE [Value] - @var END AS [Value] 
FROM TAB 

Very easy. You can set your variable to 50 an try again. Here is a fiddle for this example.

Answer 3

Change 50 with your variable

Query:

SELECT t.No,
       CASE WHEN SUM(t.Value)over(ORDER BY t.No) - 50 <0 THEN 0
           WHEN SUM(t2.Value)over(ORDER BY t2.No) > 50
                AND SUM(t.Value)over(ORDER BY t.No) >50 THEN t.Value
           ELSE SUM(t.Value)over(ORDER BY t.No) - 50
       END AS Value
FROM Table1 t
LEFT JOIN Table1 t2 ON t.No - 1 = t2.No

Answer 4

Use CTE can be one of the options

declare @Values table
(
    [No] int identity(1,1) not null,
    Value int not null
)

declare @DeductAmount int = 50

INSERT @Values VALUES (20), (10), (50), (35), (17)

;WITH cte AS
(
    SELECT *, 
        @DeductAmount -  CASE WHEN Value >= @DeductAmount THEN @DeductAmount ELSE Value END AS RemainDeductAmount, 
        Value - CASE WHEN Value >= @DeductAmount THEN @DeductAmount ELSE Value END DeductedValue 
    FROM @Values WHERE [No] = 1
    UNION ALL
    SELECT 
        v.*,  
        cte.RemainDeductAmount - CASE WHEN v.Value >= cte.RemainDeductAmount THEN cte.RemainDeductAmount ELSE v.Value END, 
        v.Value - CASE WHEN v.Value >= cte.RemainDeductAmount THEN cte.RemainDeductAmount ELSE v.Value END DeductedValue
    FROM @Values v INNER JOIN cte ON v.[No] = cte.[No] + 1
)
UPDATE target SET Value = DeductedValue
FROM @Values target INNER JOIN cte ON target.[No] = cte.[No]

SELECT * FROM @Values

The secret inside the cte

No          Value       RemainDeductAmount DeductedValue
----------- ----------- ------------------ -------------
1           20          30                 0
2           10          20                 0
3           50          0                  30
4           35          0                  35
5           17          0                  17

Answer 5

Here is another version:

DECLARE @t TABLE(ID INT IDENTITY(1, 1) , v INT )
DECLARE @x INT = 5

INSERT INTO @t VALUES ( 20 ), ( 10 ), ( 50 ), ( 35 ), ( 17 );

WITH    cte
          AS ( SELECT   * ,
                        @x - ( SELECT   ISNULL(SUM(v), 0) AS v
                               FROM     @t t2
                               WHERE    t2.ID <= t1.ID) s
               FROM     @t t1
             )
    SELECT  ID ,
            CASE WHEN s >= 0 THEN 0 
                 WHEN s < 0 AND v + s > 0 THEN -s 
                 ELSE v END
    FROM    cte