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))
sqlcuriousAsked:
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.

sqlcuriousAuthor Commented:
here is the actual query, thanks.
I am making the above change at the cte - ctecustfuncomic
0
PortletPaulfreelancerCommented:
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?
1
ste5anSenior 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)

Open in new window

1
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Scott PletcherSenior 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.
0
Mayank GairolaSr. Application Support Engg.Commented:
You can also declare a separate variable to simply store 3 * AVG(i.invoice_amt) and then use it accordingly.
1
PortletPaulfreelancerCommented:
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)
;

Open in new window

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)
;

Open in new window

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 |

Open in new window

see: http://sqlfiddle.com/#!3/9afe7/2
1

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
sqlcuriousAuthor 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
0
PortletPaulfreelancerCommented:
This does not appear to be an reasonable closure

comment #a40913247 offers no solution whatsoever and should not be the accepted solution
0
sqlcuriousAuthor 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
0
sqlcuriousAuthor Commented:
Thanks again for all your help.
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 2008

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.