Geek asks and answers

Subtract a value for multiple rows - SQL

Well, I'm stuck at the point where I need to distribute the value across multiple lines. Since I do not know a specific term, I would put it as an example below for a better understanding:

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

Table:

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

The result should look like this: (x = 20)

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

Can someone give me an idea how I can go with this? I hope my question is clear enough. If not, let me know.

Thank. Any help would be greatly appreciated.

+1
source share
3 answers

, -. value1, @X. , 0.

SQL Server 2012, . :

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;

, :

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;
+1

, SQL Server 2005 .

SQL Server 2012 SUM OVER, .

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
+2

. , , . .

, 20 5 , Value1 3 (8 + 4 + 1 + -1 + -9).

? / Value1?

: , 20 , :

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

, float, . , , - @x, ( > 1 , < 1 ). 1 2.

, , .

+1

More articles:


All Articles