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.