regarding invoice calculation

I have a clause where
If the single largest invoice is greater than 3 times the average invoices disregard this invoice from the calculation for the recommended loan.
This is what I am using but doesn't seem to work, pls give any suggestions you have

HAVING Max(DISTINCT i.invoice_amt)< 3 *(Sum(DISTINCT i.invoice_amt) / Count(DISTINCT i.invoice_no))
Who is Participating?

x
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.

Author Commented:
here is the actual query, thanks.
I am making the above change at the cte - ctecustfuncomic
Be very cautious of SUM(distinct ...)

This could be a quite misleading calculation depending on the data.

Can you please provide sample data and expected result from that data?
Senior DeveloperCommented:
Paul is right about DISTINCT, especially as a customer may have three invoices with the same amount but different invoice numbers. This would lead to 1/3 of the real average.

Just use the average:

``````HAVING MAX(i.invoice_amt) < 3 * AVG(i.invoice_amt)
``````
Senior DBACommented:
You say "disregard this invoice", but HAVING will discard the entire GROUPed result row, not just the individual row that has the too-large amount.  You need a WHERE clause to exclude a single row.  Thus, you'd have to get the average first, then use it to exclude individual rows that exceeded 3 times that.
Sr. Application Support Engg.Commented:
You can also declare a separate variable to simply store 3 * AVG(i.invoice_amt) and then use it accordingly.
For this small example:
``````CREATE TABLE YourTable
([ID] int, [invoice_amt] decimal(12,3))
;

INSERT INTO YourTable
([ID], [invoice_amt])
VALUES
(1, 12),
(2, 79),
(3, 14),
(4, 6),
(5, 6),
(6, 6),
(7, 6),
(8, 6),
(9, 6),
(10, 6)
;
``````
The following query, which uses AVG(...) OVER() so that the average is available for every row, will filter out invoice values grater than 3 times the average.
``````select
*
from (
select *, AVG(invoice_amt) OVER() as avg_inv
from YourTable
) as i
where invoice_amt < (3* avg_inv)
;
``````
The result is, note that ID 2 (\$79) is NOT listed
``````| ID | invoice_amt | avg_inv |
|----|-------------|---------|
|  1 |          12 |    14.7 |
|  3 |          14 |    14.7 |
|  4 |           6 |    14.7 |
|  5 |           6 |    14.7 |
|  6 |           6 |    14.7 |
|  7 |           6 |    14.7 |
|  8 |           6 |    14.7 |
|  9 |           6 |    14.7 |
| 10 |           6 |    14.7 |
``````
see: http://sqlfiddle.com/#!3/9afe7/2

Experts Exchange Solution brought to you by

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

Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for sqlcurious's comment #a40913247
Assisted answer: 250 points for ScottPletcher's comment #a40913588
Assisted answer: 250 points for PortletPaul's comment #a40914605

for the following reason:

thanks1
This does not appear to be an reasonable closure

comment #a40913247 offers no solution whatsoever and should not be the accepted solution
Author Commented:
Thanks a lot for all your responses, sorry my requirement changed and I had to complete everything in a hurry and travel so did not get a chance to respond. The change in requirement was that: I had to only delete that one invoice that was >3Xavg invoice, if there were multiple I do nothing. If a customer has only one outlier invoice I had to change that. So, what I did below worked, but since all of you helped me with the earlier question I will definitely assign points for all.

SELECT Customer_No,COUNT(*) total, max(invoice_amt) HighestInvoice
INTO #base3
FROM #base2
GROUP BY Customer_No

DELETE a
FROM #custfuncomic a
inner join #base2 b
on a.customer_no =b.Customer_No
AND a.Invoice_No=b.Invoice_No
INNER JOIN #base3 c
ON a.Customer_No=c.Customer_No
AND c.total=1
Author Commented:
Thanks again for all your help.
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 2008

From novice to tech pro — start learning today.