Hi everyone,

I need a query that needs to calculate average savings on the TOP 50
invoices by company.

To start, this query will give all invoices in tblinvoices:

select invoiceid, companyid, billed, repriced, savings
from tblinvoices

At first, I thought I could do just a:

select TOP 50 invoiceid, companyid, billed, repriced, savings from
tblinvoices

BUT I realized that this query will only give me just the TOP 50 records
of that query. What I want is the TOP 50 invoices by company.

Can somebody help me with this? Please?


Audrey

P.S: I know I will have to add my aggregates, etc... but I just wanted
to know how to grab 50 invoices per company. Thanks in advance.








*** Sent via Developersdex http://www.developersdex.com ***

I would think you would have at least a few inner joins to pull this
off.  All of that that data I hope would not be in one table.



Somethings to research is the northwind database look in the views for
a good example.









Audrey Ng wrote:
Show quoteHide quote

kert7***@yahoo.com wrote:
Why not? What's wrong with storing invoices in one table?

Audrey Ng wrote:
In SQL Server 2000, you would have to do a sub-query for each invoice,
and count the number of other invoices for that company that are more
recent.

e.g.

SELECT i.*
FROM  (SELECT i.InvoiceID,
                          Rank = ( SELECT COUNT(*)
                                      FROM Invoices i2
                                      WHERE i2.CompanyID = i.CompanyID
                                       AND i2.InvoiceDate >
i.InvoiceDate) + 1
            FROM  Invoices i) inv
INNER JOIN Invoices i
  ON i.InvoiceID = inv.InvoiceID
WHERE inv.Rank BETWEEN 1 AND 50

In SQL Server 2005 you can use the OVER() clause to rank each invoice
by company.

Chris

I can see this working where the number of invoices is under 50 for all
companies. How would I then handle companies that have over 50 invoices?

By the way, thanks for getting back to me so quickly.

Audrey :)


*** Sent via Developersdex http://www.developersdex.com ***

Audrey Ng wrote:
The query I posted will (or at least should, if I didn't leave any bugs
in!) return the top 50 invoices for each company. If a company has less
than 50, it will return all of them, if it has more than 50, it will
only return the most recent 50.

If your definition of 'Top 50' means something else other than '50 most
recent', then you should just modify the query to compare the
appropriate column instead of InvoiceDate.

Hope that helps,
Chris

Audrey
select invoiceid, companyid, billed, repriced, savings from
tblinvoices where invoiceid  in (select top 50 invoiceid  from tblinvoices
t where t.companyid=tblinvoices.companyid order by invoiceid  desc )





Show quoteHide quote

Huh? How's that going to work? I don't understand that subquery
select top 50 invoiceid from tblinvoices
t where t.companyid=tblinvoices.companyid order by invoiceid desc



*** Sent via Developersdex http://www.developersdex.com ***

Uri Dimant wrote:
Hmmm, much simpler than my query!

Though in SQL Server 2000 you would have to hard-code '50' rather than
allowing it to be based on a variable.

Chris

Chris,

I am using SQL Server 2000, I don't understand what you mean by
hard-code? Please explain. Thanks!

Audrey


*** Sent via Developersdex http://www.developersdex.com ***

Audrey Ng wrote:
With Uri's query, you have to put '50' in your code. With my query, you
can use a variable instead, so you could configure it to return top 50,
top 100, or whatever. Depends if you want to make it configurable, or
you always want to return top 50.

Chris

In SQL Server 2005 a TOP clause accepts variables ,  but in your case ,
you'll need to use dynamic sql



Show quoteHide quote

Post your table DDLs, sample data & expected results for others to better
understand you requirements. For details refer to: www.aspfaq.com/5006

Audrey,

Can I jump into this conversation?

SQL2005:
select companyid, avg(savings) as avgsavings
from
(select *, row_number() over (partition by companyid order by companyid,
savings desc) as rownum from tblinvoices) inv
where inv.rownum <= 50
group by companyid

SQL2000:
select com.companyid, (select avg(i.savings) from (select top 50 inv.savings
from tblInvoices inv where inv.companyid = com.companyid order by savings
desc) i) as avgsavings
from tblcompanies com

--assuming you have a table of companies - otherwise replace 'tblcompanies'
with '(select distinct companyid from tblInvoices)'

Should I explain what's going on here, or can you tell? In the SQL2005
version, we're giving each record a row_number, which resets for every
companyid. Then we can easily filter on the top 50 before getting the
average. In the SQL2000 version, we're running a separate subquery for each
company, which fetches the top 50 savings and works out the average of them.

Oh yes - I'm assuming by 'top 50', you mean 'best 50 savings'. Change the
order by clause if you need to (but make sure you order by companyid first
in the SQL2005 version).

Does this help?

Rob


Show quoteHide quote

Audrey,

select invoiceid, companyid, billed, repriced, savings
from tblinvoices t where invoiceid IN(
select top 50 invoiceid from invoiceid t1 where t.companyid =
t1.companyid
order by ---your criteria fo choosing top 50
)

*nod* But I don't like using IN when it's a list longer than just a few.

Show quoteHide quote

Hi everyone,

Thank-you for spending the time on this query. Rob: I did have a
companies table where I had to grab the TOP 50 invoices ordered by
invoice_Date desc for each company. So, I used a cursor instead to grab
all the companies, selected the TOP 50 invoices order by invoice_date
and inserted in a temp table. I then took the aggregates. Thank-you all
because you've helped with coming up with a solution.

Audrey


*** Sent via Developersdex http://www.developersdex.com ***

Denormalizing a database table
2005, Ambiguous column name, and ORDER BY
JOIN QUERY
'Transaction was deadlocked
Query Performance on Partitioned View