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

TSFLLCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
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
0
Kevin CrossChief Technology OfficerCommented:
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.
0
TSFLLCAuthor Commented:
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.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TSFLLCAuthor Commented:
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.
0
Kevin CrossChief Technology OfficerCommented:
Correct. I see now, CM is credit memos and those are payments to apply to the invoice also, so makes sense to me from my little accounting knowledge. ;)

And keep in mind, there is nothing stopping you from doing this multiple times either:
FROM accounting AS A
LEFT OUTER JOIN (
   SELECT key, ...
   FROM accounting
   WHERE accounting_type = 40
   GROUP BY key
) pmt ON pmt.key = A.key
LEFT OUTER JOIN (
   SELECT key, ...
   FROM accounting
   WHERE accounting_type = 51
   GROUP BY key
) cm ON cm.key = A.key

But you know your data best and there is a lot going on there, so if UNION is comfortable and gets you the results -- go for it. :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TSFLLCAuthor Commented:
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.
0
TSFLLCAuthor Commented:
I need to put this in my app and test fully.  Will award points if no additional problems.  Thanks.
0
Kevin CrossChief Technology OfficerCommented:
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
0
TSFLLCAuthor Commented:
Sorry about the delay.  Works like a charm.  Thanks for everything.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.