Sum values of two columns and display in next row

user_1805948 0
Viewed 65

I have a table which looks as below:

select * from #tempTable

    RN  Qty
    0   30
    1   -3
    2   -2
    3   8

Using the below query I was able to do as below:

WITH CTE AS
(SELECT ROW_NUMBER()OVER(ORDER BY RN) AS RN, Qty
FROM #tempTable)

SELECT T1.RN,T1.Qty StartingQty,T2.Qty as ChangedQty, (T1.Qty+T2.Qty) as TotalQty
FROM CTE T1 LEFT JOIN
     CTE T2 ON T1.RN=(T2.RN-1) where T2.Qty IS not null

This would result as below:

    RN  StartingQty ChangedQty  TotalQty
    1    30            -3         27
    2    -3            -2         -5
    3    -2             8          6

The result should look as below and not sure how to achieve this:

    RN  StartingQty ChangedQty  TotalQty
    1    30            -3         27
    2    27            -2         25
    3    25             8         33

Any leads would be helpful as of how to achieve this.

window-functions sum sql-server sql
2 Answers

You may try an approach using LEAD() and windowed SUM():

SELECT *
FROM (
   SELECT
      RN,
      SUM(Qty) OVER (ORDER BY RN) AS StartingQty,
      LEAD(Qty) OVER (ORDER BY RN) AS ChangedQty,
      SUM(Qty) OVER (ORDER BY RN) + LEAD(Qty) OVER (ORDER BY RN) AS TotalQty
   FROM (VALUES
      (0, 30),
      (1, -3),
      (2, -2),
      (3, 8)
   ) v (RN, Qty)
) t
WHERE ChangedQty IS NOT NULL

Result:

RN  StartingQty ChangedQty  TotalQty
0            30         -3        27
1            27         -2        25
2            25          8        33
user_65780800

You can get the basic values using window functions and window frames:

select rn, qty as StartingQty,
       lead(qty) over (order by rn) as ChangedQty,
       sum(qty) over (order by rn rows between unbounded preceding and 1 following) as TotalQty
from (values (0, 30),
             (1, -3),
             (2, -2),
             (3, 8)
     ) v (rn, Qty);

To eliminate the last row, alas, you need a subquery:

select t.*
from (select rn, qty as StartingQty,
             lead(qty) over (order by rn) as ChangedQty,
             sum(qty) over (order by rn rows between unbounded preceding and 1 following) as TotalQty
      from (values (0, 30),
                   (1, -3),
                   (2, -2),
                   (3, 8)
           ) v (rn, Qty)
     ) t
where changedqty is not null;

Here is a db<>fiddle.

user_11440350
Related