asked on

SUM(amount) generates amount * 2 instead of amount with multiple left join tables

I have an accounts receivable SQL select statement as included below.
The actual records should generate the aging balances using (invoice - payment - credit_memo_amount_applied) with the appropriate 'As of Date'.

The example records I'm working with are for one customer.
One invoice (accounting_type = 10, 20 or 30 based on the type of invoice)
Two payments (accounting_type = 40)
One credit memo amount being applied (accounting_type = 51)

The problem with my logic (I THINK) is that because there are two payment for this invoice, credit_memo_amount_applied is being doubled. If I use MAX(cm.credit_memo_amount_applied) the balance = 0 as it should. But this will not work because there can be multiple credit memos as opposed to one in this situation and you would only pick up the MAX value of the two credit memos.

Any thoughts about how to solve? If I try to create multiple selects inside each 'aging' this select statement could get out of hand and become extremely slow?

Thanks in advance.

"SELECT ar.company_id, ar.contact_id, contact.contact_index, ar.property_id, p.property_description, " & IIf(glReportDescription = "A/R Summary by Assessment Period", "ar.assessment_period, ", "") & "SUM(BalCurrent) AS BalanceCurrent, SUM(Bal1to30) AS Balance0to30, SUM(Bal31to60) AS Balance31to60, SUM(Bal61to90) " & _
"AS Balance61to90, SUM(BalOver90) AS BalanceOver90, SUM(CRBalance) as credit_memo_balance " & _
"FROM (SELECT a.company_id, a.contact_id, a.property_id, a.accounting_date, a.due_date, a.invoice_id, " & _
"CASE WHEN DATEDIFF(dd, a.due_date, CONVERT(datetime, '" & glFromAccDate & "')) <= 0 THEN MAX(a.invoice_amount) - CASE WHEN SUM(pmt.payment_amount) IS NULL " & _
"THEN 0 ELSE SUM(pmt.payment_amount) END - CASE WHEN SUM(cm.credit_memo_amount_applied) IS NULL THEN 0 ELSE SUM(cm.credit_memo_amount_applied) END ELSE 0 END AS BalCurrent, " & _
"CASE WHEN DATEDIFF(dd, a.due_date, CONVERT(datetime, '" & glFromAccDate & "')) BETWEEN 1 AND 30 THEN MAX(a.invoice_amount) - CASE WHEN SUM(pmt.payment_amount) IS NULL " & _
"THEN 0 ELSE SUM(pmt.payment_amount) END - CASE WHEN SUM(cm.credit_memo_amount_applied) IS NULL THEN 0 ELSE SUM(cm.credit_memo_amount_applied) END ELSE 0 END AS Bal1to30, " & _
"CASE WHEN DATEDIFF(dd, a.due_date, CONVERT(datetime, '" & glFromAccDate & "')) BETWEEN 31 AND 60 THEN MAX(a.invoice_amount) - CASE WHEN SUM(pmt.payment_amount) IS NULL " & _
"THEN 0 ELSE SUM(pmt.payment_amount) END - CASE WHEN SUM(cm.credit_memo_amount_applied) IS NULL THEN 0 ELSE SUM(cm.credit_memo_amount_applied) END ELSE 0 END AS Bal31to60, " & _
"CASE WHEN DATEDIFF(dd, a.due_date, CONVERT(datetime, '" & glFromAccDate & "')) BETWEEN 61 AND 90 THEN MAX(a.invoice_amount) - CASE WHEN SUM(pmt.payment_amount) IS NULL " & _
"THEN 0 ELSE SUM(pmt.payment_amount) END - CASE WHEN SUM(cm.credit_memo_amount_applied) IS NULL THEN 0 ELSE SUM(cm.credit_memo_amount_applied) END ELSE 0 END AS Bal61to90, " & _
"CASE WHEN DATEDIFF(dd, a.due_date, CONVERT(datetime, '" & glFromAccDate & "')) > 90 THEN MAX(a.invoice_amount) - CASE WHEN SUM(pmt.payment_amount) IS NULL " & _
"THEN 0 ELSE SUM(pmt.payment_amount) END - CASE WHEN SUM(cm.credit_memo_amount_applied) IS NULL THEN 0 ELSE SUM(cm.credit_memo_amount_applied) END ELSE 0 END AS BalOver90, " & _
"CASE WHEN SUM(cm.credit_memo_balance) IS NULL THEN 0 ELSE SUM(cm.credit_memo_balance) END AS CRBalance, a.assessment_category_id" & IIf(glReportDescription = "A/R Summary by Assessment Period", ", a.assessment_period ", " ") & _
"FROM accounting AS a " & _
"LEFT OUTER JOIN accounting AS pmt ON pmt.accounting_type = 40 AND 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, '" & glFromAccDate & "') " & _
"LEFT OUTER JOIN accounting AS cm ON cm.accounting_type = 51 AND 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, '" & glFromAccDate & "') " & _
"WHERE (a.company_id = " & glCompany & ") AND (a.accounting_type <= 30) " & IIf(xFieldCriteria = "", "", "AND " & xFieldCriteria) & " " & _
"GROUP BY a.company_id, a.contact_id, a.property_id, a.accounting_type, a.accounting_date, a.due_date, a.invoice_id, a.assessment_category_id" & IIf(glReportDescription = "A/R Summary by Assessment Period", ", a.assessment_period ", " ") & _
") AS ar " & _
"INNER JOIN contact ON ar.company_id = contact.company_id AND ar.contact_id = contact.contact_id " & _
"INNER JOIN properties p ON ar.company_id = p.company_id AND ar.property_id = p.property_id " & _
"GROUP BY ar.company_id, ar.contact_id, contact.contact_index, ar.property_id, p.property_description" & IIf(glReportDescription = "A/R Summary by Assessment Period", ", ar.assessment_period ", " ") & _
IIf(chkExcludeZeroBalances.Checked, " HAVING " & xExcludeZeroBalanceScript & IIf(chkAccountBalance.Checked, " AND " & xAccountBalanceScript, ""), IIf(chkAccountBalance.Checked, " HAVING " & xAccountBalanceScript & "", "")) & xReportFieldOrder

Open in new window

UnifiedIS

I would look to isolate your aggregate queries from your left joins, loosely like this:

SELECT A.*
FROM (SELECT SUM(Amount), key
FROM table
GROUP BY key...) A
LEFT OUTER JOIN accounting AS pmt...
ON A.key = pmt.key
LEFT OUTER JOIN accounting AS CM...
ON A.key = pmt.key

key represents your join fields

Kevin Cross

Yes, I think you are correct. If you have a possibility that the JOIN will yield duplicates, then you need to account for that. One good way is similar to what UnifiedIS has shown. However, from the question it appears you want to do the derived SUM on the "accounting AS pmt" bit.

i.e.,
FROM accounting AS A
LEFT OUTER JOIN (
SELECT key, ...
FROM accounting
GROUP BY key
) pmt ON pmt.key = A.key
...

Hope that makes sense.

TSFLLC

ASKER

I am now trying to break the sum of payments and sum of credit amounts by invoice by performing a UNION ALL. Then I can encapsulate those results in another select performing a MAX(invoice_amount) - MAX(pmt) - MAX(cm). If I don't get rid of a LEFT OUTER on both in the same select I don't see how I will get an accurate SUM if there are more pmts than credits and the opposite.

TSFLLC

ASKER

It always seems to help when I stop putting my fingers on the keyboard and start with PAPER.

I think this should work like a charm.

1) Select the pmts & credits with a UNION (this creates full list of appropriate pmts & credits)
2) With select #1, select sum of pmts & credits (this creates one rec per invoice)
3) Select invoice_id/invoice_amount, CASE & date to put in appropriate aging. ALSO perform LEFT OUTER
on select #2 keying on invoice_id and subtract the LEFT JOIN single pmt and/or credit from invoice amount

msvisa1 - This is along the lines of what you proposed but the UNION allows me to SUM(pmts & credits) without issue.

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TSFLLC

ASKER

mwvisa1,

I DO like your ideal about the separate left joins on the selects for each pmts & credits. I've incorporated that structure in a GOOD, WORKING statement. Also, note that I included a 3rd LEFT JOIN to pull an OPEN credit balance. Like QuickBooks, if a credit memo has not been applied although it exists, it will still show up on A/R with an open account balance. Purpose being, it informs the user a credit memo may not be fully applied.

Thanks to both of you for the help but mwvisa1 hit the nail on the head.

TSFLLC

ASKER

I need to put this in my app and test fully. Will award points if no additional problems. Thanks.

Kevin Cross

Good luck! Let us know if you find anything that needs further correction, but sounds like you have the bases covered. Unapplied cash is a good thing to include, especially when someone is reviewing A/R for collection purposes. Seeing a 180-day past due invoice might invoke a nasty call to a customer only to find that they have $10,000 sitting on their account...whoops. :) Ah, the joys of accounting.

Best regards and happy coding,

Kevin

TSFLLC

ASKER

Sorry about the delay. Works like a charm. Thanks for everything.