I would like to write an SQL query that calculates the total of outstanding invoices on a daily basis, but couldn't find a solution as to how to do it so far. My table includes an invoice amount, a creation day and a repayment day (which is empty if no repayment has been received yet).

In order to calculate the outstanding invoice amount, I need to get the sum of all amounts that have not been repaid, i.e. the repayment day is empty or after the focal day. Here is an example:

Now I wish to calculate the outstanding invoice amount at the end of each day. I would like to have a table returned that looks like the following. The amounts that have been repaid are excluded from the daily total.

Would love to get some input on this.

Thanks!

CodePudding user response：

The first step is to split the two things that can happen in a single record into two separate rows (creation and optional repayment). You do this by "looping" over the table twice, looking at it for creation and then looking at it for repayment:

select
    case payment
        when 1 then repayment_date
        else creation_date
    end date,
    case payment
        when 1 then -amount
        else amount
    end amount
from (select 0 payment union all select 1 payment) payment
join invoice on payment=0 or repayment_date

Then you take the results of that, group by and sum amounts for each date, and use a window sum to get a running total of per date sums:

select
    date_format(date,'%e-%c-%Y') Date,
    sum(sum(amount)) over (order by date) 'Outstanding invoice'
from (
    select
        case
            when payment then repayment_date
            else creation_date
        end date,
        case
            when payment then -amount
            else amount
        end amount
    from (select 0 payment union all select 1 payment) payment
    join invoice on payment=0 or repayment_date
) activity
group by date

fiddle