By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
CREATE TABLE loans (
id int
, NextPayment int
, FullPaymentConfirmed int
, MonthlyPaymentRate int
);
CREATE TABLE transactions ( id int, amount int );
WITH RemainingLoans AS(SELECT SUM(TIMESTAMPDIFF(MONTH, l.NextPayment, l.FullPaymentConfirmed) * l.MonthlyPaymentRate) AS RemainingPayments FROM loans AS l)
, CurrentBalances AS(SELECT SUM(t.amount) AS allBalances FROM transactions AS t)
SELECT l.RemainingPayments - b.allBalances AS TotalOutstandings FROM RemainingLoans AS l, CurrentBalances AS b
;
| TotalOutstandings |
|---|
| null |
WITH RemainingLoans AS (
SELECT SUM(TIMESTAMPDIFF(MONTH, l.NextPayment, l.FullPaymentConfirmed) * l.MonthlyPaymentRate) AS RemainingPayments
FROM loans AS l
)
, CurrentBalances AS (
SELECT SUM(t.amount) AS allBalances
FROM transactions AS t
)
SELECT l.RemainingPayments - b.allBalances AS TotalOutstandings
FROM RemainingLoans AS l
, CurrentBalances AS b
;
| TotalOutstandings |
|---|
| null |