Incorrect amount returned in ''having'' statement
-
Can anyone tell me why the:
HAVING sum (accounts_receivable_history.amount) + sum(accounts_receivable_memo.amount) <> 0
does not calculate the correct amounts in the query listed below?
--List Credit_Card_Bank_Number Entity_Number whose AR balance is paid off.
SELECT credit_card_bank_number.entity_number,
credit_card_bank_number.fund_year,
credit_card_bank_number.fund_code,
sum(accounts_receivable_history.amount) + sum(accounts_receivable_memo.amount) AS balance_due
FROM credit_card_bank_number
INNER JOIN accounts_receivable_history
ON accounts_receivable_history.entity_number = credit_card_bank_number.entity_number
AND accounts_receivable_history.fund_code = credit_card_bank_number.fund_code
AND accounts_receivable_history.fund_year = credit_card_bank_number.fund_year
INNER JOIN accounts_receivable_memo
ON accounts_receivable_memo.entity_number = credit_card_bank_number.entity_number
AND accounts_receivable_memo.fund_code = credit_card_bank_number.fund_code
AND accounts_receivable_memo.fund_year = credit_card_bank_number.fund_year
GROUP BY credit_card_bank_number.entity_number,
credit_card_bank_number.fund_year,
credit_card_bank_number.fund_code
HAVING sum(accounts_receivable_history.amount) + sum(accounts_receivable_memo.amount) <> 0
ORDER BY credit_card_bank_number.entity_number,
credit_card_bank_number.fund_year,
credit_card_bank_number.fund_code
Thank you
Howard
-
Without data, Table structures, and what you think the value should be it is impossible to "Guess" what the problem could be.
-
Ray M is right. nevertheless, in your case, problems are obvious at a distance 😉 and you can see them by yourself when you omit the grouping. you'll find out that there are too many records in the non-grouped resultset. why? because your double inner joins *multiply* each other, if there is more than one record per table and creditcard in the joined tables.
by the way: i sell table aliases at reasonable prices
_/_/_/ paramind _/_/_/ -
Ray M
You are correct in that there are many records in the non-grouped resultset.
Any suggestions on how to correct this?
Howard
-
Though I'm not Ray M .... 😉 and I did not say many, I said TOO many.
As we are talking of funds - mine would love some improvement 😉
SELECT CCBN.entity_number,
CCBN.fund_year,
CCBN.fund_code,
ACM.amount + ACH.amount AS balance_due
FROM credit_card_bank_number AS CCBN
INNER JOIN (SELECT
entity_number,
fund_code,
fund_year,
SUM(amount) AS Amount
FROM accounts_receivable_history
GROUP BY
entity_number,
fund_code,
fund_year) AS ACH
ON CCBN.entity_number = ACH.entity_number
CCBN.fund_code = ACH.fund_code
CCBN.fund_year = ACH.fund_year
INNER JOIN (SELECT
entity_number,
fund_code,
fund_year,
SUM(amount) AS Amount
FROM accounts_receivable_memo
GROUP BY
entity_number,
fund_code,
fund_year) AS ACM
ON CCBN.entity_number = ACM.entity_number
CCBN.fund_code = ACM.fund_code
CCBN.fund_year = ACM.fund_year
WHERE
ACM.Amount <> ACHAmount -- the same as ACM.Amount + ACHAmount <> 0
ORDER BY
CCBN.entity_number,
CCBN.fund_code,
CCBN.fund_year
_/_/_/ paramind _/_/_/ -
paramind
Thank you, your solution works. Now my problem is that the total of the items returned that equal zero and do not equal zero are not the total rows in the credit_card_bank_number table.
What is returned from your query if a row in the credit_card_bank_number table has no matches in the join tables or a match in one join table and not the other join table?
Howard
-
>What is returned from your query if a row in the credit_card_bank_number table has no matches in the join tables or a match in one join table and not the other join table?<
That's what outer joins are good for
I just didn't modify THAT because I didn'twant to modify your assumed retrieval logic.If you outer join both tables, you will sometimes have a NULL result. If so, check for NULL appropriately. You may want to check, why the rows have been selected - that's what the two new columns are for.
SELECT CCBN.entity_number,
CCBN.fund_year,
CCBN.fund_code,
ISNULL(ACM.amount,0) + ISNULL(ACH.amount,0) AS balance_due,
-- ACM.amount AS viewNULLvs0ACM,
-- ACH.amount AS viewNULLvs0ACH
FROM credit_card_bank_number AS CCBN
LEFT OUTER JOIN (SELECT
entity_number,
fund_code,
fund_year,
SUM(amount) AS Amount
FROM accounts_receivable_history
GROUP BY
entity_number,
fund_code,
fund_year) AS ACH
ON CCBN.entity_number = ACH.entity_number
CCBN.fund_code = ACH.fund_code
CCBN.fund_year = ACH.fund_year
LEFT OUTER JOIN (SELECT
entity_number,
fund_code,
fund_year,
SUM(amount) AS Amount
FROM accounts_receivable_memo
GROUP BY
entity_number,
fund_code,
fund_year) AS ACM
ON CCBN.entity_number = ACM.entity_number
CCBN.fund_code = ACM.fund_code
CCBN.fund_year = ACM.fund_year
WHERE
ISNULL(ACM.Amount,0) <> ISNULL(ACHAmount,0)
ORDER BY
CCBN.entity_number,
CCBN.fund_code,
CCBN.fund_year
Can we now talk of funds?
_/_/_/ paramind _/_/_/ -
paramind
Thank you, thank you, and thank you.
Your example is exactly what I was looking for.
The Fund referred to in the query refers to funds for a non-profit community center. Fund 01 is membership, fund 06 is camp, etc.
Howard
-
😉 I'm born non-profit
_/_/_/ paramind _/_/_/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply