TSFLLC
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
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
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.
The actual records should generate the aging balances using (invoice - payment - credit_memo_amount_applied
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
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
Last Comment
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.
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.
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.
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,
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.
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,
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
ASKER
I need to put this in my app and test fully. Will award points if no additional problems. Thanks.
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
Best regards and happy coding,
Kevin
ASKER
Sorry about the delay. Works like a charm. Thanks for everything.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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