Max and Avg debt days over a period of time

I have invoices pending payment, every invoice has two dates, first when the invoice is required to pay and the other when the invoice is paid. I want to know in a period of time the max debt and the avg debt

This is the table

Id          Invoice Amount      InvoiceDate InvoicePayment
----------- ------- ----------- ----------- -------------
1           Bill 1  314         2019-01-20  2019-03-01
2           Bill 2  205         2019-01-14  2019-02-18
3           Bill 3  90          2019-02-04  2019-02-06
4           Bill 4  456         2019-01-03  2019-04-27

I would like to know the max debt amount in february and the avg debt

Answer

You can unpivot with cross apply, and use a window sum to compute the “running” debt at each given point in time. The rest is just filtering and aggregation:

select avg(debt) avg_debt, max(debt) max_debt
from (
    select x.dt, sum(x.amount) over(order by x.dt) debt
    from mytable t
    cross apply (values (invoicedate, amount), (invoicepayment, -amount)) as x(dt, amount)
) t
where dt >= '20200201' and dt < '20200301'