Despite my efforts I cannot think of a way to answer my need :
I have 2 tables containing respectively a set of loans to be reimbursed, and a set of reimbursements on these loans (not all loans have an entry in reimbursement table because some of them remain unpaid).
Table loan has columns:
id
amount
due_date
Table reimbursement has columns:
id
debt_id
payment_date
My goal is to obtain the amount that was overdue for more than 5 days for each date in the past.
Here is where I am at (in Postgres grammar):
with due_for_5_days_by_date AS (
select
due_date + interval '5' DAY as due_date,
sum(amount) as amount
FROM loan
GROUP BY due_date
),
paid_by_date as (
select
payment_date,
sum(amount) as amount
FROM reimbursement
join loan on loan.id = loan_id
GROUP BY payment_date
),
cumulated as (
SELECT
COALESCE(due_date, payment_date) as date,
SUM(COALESCE(paid_by_date.amount, 0)) over (order by COALESCE(due_date, payment_date)) as paid,
SUM(COALESCE(due_for_5_days_by_date.amount, 0)) over (order by COALESCE(due_date, payment_date)) as due_5d
FROM due_for_5_days_by_date
FULL OUTER JOIN paid_by_date on due_date = payment_date
)
select
DATE,
due_5d - paid as overdue_5d
from cumulated
order by date
Explanation :
First I get the amount due for exactly 5 days for each date
Then, I get the amount paid for each date
Then I sum the amounts obtained at 1. and 2. to get the cumulative due and paid amounts
Then by difference I get the overdue amount
But the problem is that, by doing this, the amount paid by date also contains reimbursement of loans expired for less than 5 days. In other words the paid amount for each date in the final query is overestimated as it should exclude the amount received on loans recently expired on that date. I cannot think of a way to fix this.
Is this the type of loan where you repay in more than one installment? (eg., you might take out loan for a car for $10,000 and pay it over a period of 3 years in monthly installments). If that is the case, your tables don't have enough data to represent such a loan and the repayment schedule.
So, in the following, I am going to assume that this is a loan which you repay in just one installment (e.g., you borrowed $10,000 and 3 months later, you pay it back with interest perhaps). Even in that case, you need to account for partial payments etc., don't you? In that case, you could do something like shown below:
SELECT
L.Id,
L.Amount,
L.Due_Date,
R.Payment_Date,
IsLate =
CASE
WHEN DATEDIFF(DAY,L.Due_Date,COALESCE(R.Payment_date,'20991231')) > 5 THEN 'True'
ELSE 'False'
END
FROM
dbo.Loan AS L
LEFT JOIN dbo.Reimbursement as R ON
R.Id = L.Id;
Hi @JamesK ,
Yes I simplified the problem to focus on my core issue.
Your query just gives me whether a loan was late or not. What I need is the total overdue amount at any point in time.
I think I found a way to do it, but it uses an expensive cross join, so I am still looking for a better solution:
with all_dates as (
select distinct payment_date as date
from reimbursement
union
select distinct due_date as date
from loan
union
select distinct due_date + interval '5' day as date
from loan
),
joined_data as (
SELECT
amount,
due_date,
payment_date
from loan
LEFT join reimbursement on loan.id = loan_id
)
select
date,
SUM(
CASE
WHEN (due_date <= DATE - INTERVAL '5' day) and (payment_date > DATE or payment_date is null) then amount
else 0
END
) as overdue_5d
FROM all_dates
cross join joined_data
group by date
order by DATE
I don't know Postgres SQL syntax, but from Google, it looks like this should get the individual loan overdue amounts. If that looks correct, then you'd just need to add an outer query to sum them up.
I didn't know how to get current date in Postgres, so I just ?...? to indicate that.
SELECT l.id, l.amount, r.amount_paid, l.amount - r.amount_paid amount_overdue
FROM loan l
LEFT JOIN LATERAL (
SELECT SUM(amount) AS amount_paid
FROM reimbursement r
WHERE r.debt_id = l.id AND r.payment_date <= l.due_date + interval '5' DAY
) r ON r.amount_paid < l.amount
WHERE l.due_date + interval '5' DAY < {?todays_date?}