I have tried many of the scripts that I found here but none did what I required.
I have 3 tables:
I would like to:
- sum all invoices per client within a date range
- sum all payments for clients with in the date range then subtract payments from invoiced to show amount outstanding.
I need to add the date range and get the total if not zero.
So far my query is
(select sum(Total) from invoiced where invoiced.ClientId = clients.Id) AS Invoiced,
(select sum(Amount) from payments where payments.ClientId = clients.Id) AS Paid
Best How To :
The problem is the non existing relation between the invoice and the payments. This makes it a bit "dirtier" ;)
select p.id, sum(i.total), sum(p.amount), sum(i.total) - sum(p.amount) as outstanding
select c.id, coalesce(pay.amount,0) as amount
(select ClientId, Sum(Amount) amount from payments group by ClientId) as pay
right join clients c on c.id = pay.ClientId
select c.id, inv.total
(select ClientId, Sum(Total) total from invoiced group by ClientId) as inv
left join clients c on c.id = inv.ClientId
on p.id = i.id
group by id
having sum(i.total) <> sum(p.amount)
Updated Sqlfiddle with results here