[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-05
9
Medium Priority
?
208 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:TSFLLC
  • 5
  • 3
9 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 36917855
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36918250
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
 

Author Comment

by:TSFLLC
ID: 36918810
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:TSFLLC
ID: 36918948
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36919001
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
 

Author Comment

by:TSFLLC
ID: 36919370
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
 

Author Comment

by:TSFLLC
ID: 36919375
I need to put this in my app and test fully.  Will award points if no additional problems.  Thanks.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36919454
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
 

Author Closing Comment

by:TSFLLC
ID: 37018087
Sorry about the delay.  Works like a charm.  Thanks for everything.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question