sql - sum of differencies of rows

Question

I have example values in column like this:

    values 
    -------
    89    
    65    
    56    
    78    
    74   
    73    
    45    
    23    
    5    
    654   
    643   
    543   
    345   
    255   
    233   
    109   
    43    
    23    
    2    

The values are rising up and then fall down to 0 and rising up again. I need to count differencies between rows in new column and the sum of these differencies too (cumulative sum) for all values. The values 56 and 5 are new differencies from zero
The sum is 819. Example from bottom> (23-2)+(43-23)+(109-43)+..+(654-643)+(5)+(23-5)+..

Answer 1

Okay, here is my try. However, you need to add an Identity field (which I called "AddSequence") that starts with 1 for the first value ("2") and increments by one for every other value.

SELECT SUM(C.Diff) FROM
(
 SELECT CASE WHEN (A.[Value] - (SELECT [Value] FROM [TestValue] AS B WHERE B.[AddSequence]= A.[AddSequence]-1)) > 0
    THEN (A.[Value] - (SELECT [Value] FROM [TestValue] AS D WHERE D.[AddSequence]= A.[AddSequence]-1))
    ELSE 0
    END AS Diff
 FROM [TestValue] AS A
) AS C

The first solution I had neglected that fact that we had to start over whenever the difference was negative.