Add values from previous line to next

Question

I wonder if there is a way to add up the values of a line with its previous.

Ex: In select below, the result I’m getting is not doing the accumulated

**SELECT DATMOV, VLRMOV, DEBCRE,
(CASE WHEN E600MCC.DEBCRE = 'C' THEN (VLRMOV) ELSE (VLRMOV * -1) END) AS RESULT 
FROM E600MCC
GROUP BY  DATMOV, VLRMOV, DEBCRE, E600MCC.SEQMOV, E600MCC.DEBCRE
ORDER BY  DATMOV ASC**

- DatMov                         VlrMov     DebCre    Acumulado
- 20 13-11-01 00:00:00.000        15          C          15
- 2013-11-01 00:00:00.000         10          C          10
2013-11-01 00:00:00.000         -5          D          -5
2013-11-01 00:00:00.000         12          C          12
2013-11-04 00:00:00.000        -20          D          -20
2013-11-04 00:00:00.000         50          C          50

I wanted the accumulated column to always be the sum of the current value with the previous one.

Answer 1

You need to use the window functions (window functions) to make a cumulative sum. They are available in SQL Server as of 2012. To do with an earlier version, you will get a little more work.

The syntax of a cumulative sum is the following: SUM(expression) OVER (ordering clauses and sum limits)

GROUP BY doesn’t seem necessary either. Then your SQL would be:

SELECT DATMOV, VLRMOV, DEBCRE,
  SUM(CASE WHEN DEBCRE = 'C' THEN VLRMOV ELSE -VLRMOV END)
    OVER(ORDER BY DATMOV ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) [RESULT]
FROM E600MCC
ORDER BY DATMOV

The syntax is a bit ugly, due to the window options that need to be specified. https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Cumulative-Sum-b1c40b0d