Running Difference between two columns

Question

I want to calculate a running-difference when saldo 1 is below saldo 2.

Below you can find my dataset

I wish to obtain the following result

I have tried so many things with subqueries and different ways of finding the difference. I have found many examples of running-totals with one value and so. But I cannot get my conditinal statements to work and if I just do the clean code without the conditional statements, my values are always wrong.

Here is my code at the moment. I am using partition as I want to reset when ID change.

IF SALDO1 < SALDO2
BEGIN
SELECT ID, SALDO1, SALDO2, 
    SUM(SALDO2 - SALDO1) over (PARTITION BY ID ORDER BY ID) as RunningDifference
END

ELSE 
BEGIN 
   '0'  
END

FROM test2;

Thank you!

Answer 1

You seem to want conditional statements around and within the window sum:

select id, saldo1, saldo2, 
    case when saldo1 < saldo2   
        then sum(case when saldo1 < saldo2 then saldo2 - saldo1 else 0 end) 
            over (partition by id order by ordering_id) 
        else 0
    end as runningdifference
from test2

If your database supports greatest(), we can shorten the inner expression:

select id, saldo1, saldo2, 
    case when saldo1 < saldo2   
        then sum(greatest(saldo2 - saldo1, 0)) over (partition by id order by ordering_id) 
        else 0
    end as runningdifference
from test2

Note that your over() clause is not stable: partition by id order by id does not provide a consistent ordering criteria of rows within the partition: all rows are ties, so all end up summed together. You need a deterministic sorting criteria to achieve the result you want, I assumed ordering_id.

Answer 2

You seem to what the running difference when saldo2 is greater than saldo. That would be

select t.*,
       (case when saldo2 > saldo1
             then sum(case when saldo2 > saldo1 then saldo2 - saldo1 else 0 end) over (partition by id order by <ordering column>)
             else 0
        end)
from test2 t;

Note that your question presupposes a column that expresses the ordering. That should be the argument to the order by, not id (which is used for partitioning). SQL tables represent unordered sets. There is no ordering unless a column specifies that information.