Ok, this one's fucking me off.
Scenario: Producing a report of clients that have not paid their bloody invoices, with outstanding amounts split up into 30 day balance, 60 day balance etc.
Problem: Everything works fine,
except where the client has made more than one payment (which obviously will end up being all of them) - the totals are multiplied by the number of payments.
Current SQL:
Code:
SELECT
a.clientid, a.client_name,
SUM(a.balance) AS total,
SUM(a.balance30) AS total30,
SUM(a.balance60) AS total60,
SUM(a.balance90) AS total90,
SUM(a.balance120) AS total120,
SUM(a.balance_over) AS total_over,
MAX(p.datereceived) AS last_payment_date
FROM (
SELECT
i.clientid, c.NAME AS client_name,
i.outstanding_total AS balance,
CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) <= 30 ) THEN i.outstanding_total ELSE 0 END AS balance30,
CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 30 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 60 ) THEN i.outstanding_total ELSE 0 END AS balance60,
CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 60 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 90 ) THEN i.outstanding_total ELSE 0 END AS balance90,
CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 90 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 120 ) THEN i.outstanding_total ELSE 0 END AS balance120,
CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 120 ) THEN i.outstanding_total ELSE 0 END AS balance_over
FROM accounts_invoices i
INNER JOIN clients c ON i.clientid=c.clientid
WHERE
i.outstanding_total > 0 AND i.dateinvoiced <= '2019-03-21'
) AS a
LEFT JOIN accounts_payments p ON a.clientid=p.clientid
GROUP BY a.clientid
ORDER BY client_name
I'm assuming it's something to do with the JOIN on accounts_payments being outside of the subquery but I can't seem to get it to work with it inside the subquery (it has to be a LEFT JOIN so that it also picks up clients that have made no payments, incidentally)
Halp!