I'm apparently confused about the way SQL works with a LEFT OUTER JOIN. Below is a sample statement I am using for summing InvAmt, PmtAmt, CreditMemoAmt. Payments and credits are based on a join of Invoices.
SELECT a.contact_id, a.property_id, a.invoice_id,
MAX(a.invoice_amount) AS InvoiceAmt,
CASE WHEN SUM(pmt.payment_amount) IS NULL THEN 0 ELSE SUM(pmt.payment_amount) END AS PmtAmt,
CASE WHEN SUM(cm.credit_memo_amount_
applied) IS NULL THEN 0 ELSE SUM(cm.credit_memo_amount_
applied) END AS CMAmt
FROM accounting_link_invoice AS a
LEFT OUTER JOIN accounting_link_payment AS pmt ON a.contact_id = pmt.contact_id AND a.property_id = pmt.property_id
AND a.invoice_id = pmt.invoice_id AND pmt.accounting_date <= CONVERT(datetime, '05/22/2007')
LEFT OUTER JOIN accounting_link_credit AS cm ON a.contact_id = cm.contact_id AND a.property_id = cm.property_id
AND a.invoice_id = cm.invoice_id AND cm.accounting_date <= CONVERT(datetime, '05/22/2007')
WHERE (a.property_id = 921) AND (a.accounting_date <= CONVERT(datetime, '05/22/2007')) AND (a.contact_id = 929)
GROUP BY a.contact_id, a.property_id, a.invoice_id
All of my numbers come out correct except for ONE invoice where there are BOTH payments and credits associated with it. There are (3) payment records and one credit_memo record. The sum of CMAmt = 3 * credit_memo_amount_applied
instead of 1 * credit_memo_amount_applied
.
What do I have to do with this select statement to get an independent sum of payments and independent sum of credits for each invoice??
Start Free Trial
