Subtract value to multiple rows - SQL

Question

Well I am stuck at a point where I need to distribute a value across multiple rows. Since I do not know the specific term, I would put it in the form of example below for better understanding:

Assuming the value of x to be 20, I need to distribute/subtract it to rows in descending order.

TABLE:

ID        Value1 
1          6             
2          5
3          4
4          3
5          9

Result should look like: (x=20)

ID        Value1     Answer
1          6           14    
2          5           9
3          4           5
4          3           2
5          9           0

Can anyone just give me an idea how I could go with this? I hope my question is clear enough. If not please let me know.

Thanks. Any help would be greatly appreciated.

Answer 1

It is perhaps easier to think of this problem in a different way. You want to calculate the cumulative sum of value1 and then subtract that value from @X. If the difference is negative, then put in 0.

If you are using SQL Server 2012, then you have cumulative sum built-in. You can do this as:

select id, value1,
       (case when @X - cumvalue1 < 0 then 0 else @X - cumvalue1 end) as answer
from (select id, value1,
             sum(value1) over (order by id) as cumvalue1
      from table t
     ) t;

If you don't have cumulative sum, you can do this with a subquery instead:

select id, value1,
       (case when @X - cumvalue1 < 0 then 0 else @X - cumvalue1 end) as answer
from (select id, value1,
             (select sum(value1)
              from table t2
              where t2.id <= t.id
             ) as cumvalue1
      from table t
     ) t;

Answer 2

I don't understand your question. I know what I think you're trying to do. But your example doesn't make sense.

You say you want to distribute 20 over the 5 rows, yet the sum of the difference between Value1 and Answer is only 3 (8+4+1+-1+-9).

And how do you want to distribute the values? Using a spread/split based on the value in Value1?

Edit: I made an example which splits 20 over the values you've specified above:

DECLARE @x FLOAT = 20.0

DECLARE @values TABLE (
  ID INT,
  VALUE FLOAT,
  NEWVAL FLOAT)

INSERT INTO @values (ID, VALUE) VALUES (1,6), (2,5),(3,4),(4,3),(5,9)

UPDATE f
SET [NEWVAL] = [newValue]
FROM @values f
INNER JOIN (
    SELECT 
        ID,
        value + ((VALUE / [maxValue]) * @x) [newValue]
    FROM
        @values
        CROSS APPLY (
            SELECT
                SUM(value) [maxValue]
            FROM
                @values
        ) m 
) a ON a.ID = f.ID

SELECT * FROM @values

Unfortunately I had to change your values to floats for this to work. If you require them as integers, you'll need to use rounding and then calculate the difference of the sum of new value - @x and then spread the difference over the rows (if > 1 then add to lowest number, if < 1 subtract from largest value). Your rounding should be usually just 1 or 2.

I don't even know if I this is what you're trying to do yet.

Answer 3

Untested for syntax, but the idea should work in SQL Server 2005 and newer.

SQL Server 2012 has SUM OVER clause which makes this even handier.

SELECT ID, Value1, CASE WHEN 20-SumA < 0 THEN 0 ELSE 20-SumA END AS Answer
FROM TABLE A
CROSS APPLY (SELECT SUM(B.Answer) SumA FROM TABLE B
   WHERE B.ID <= A.ID) CA