Top 10 -- with a twist.

[markinro]

Ok, all you gurus, try this one. I am generating a top 10 revenue report in the following manner.

For each month in the past year...
INSERT INTO #topcust
SELECT TOP 10 customer FROM REVENUES
WHERE DATEDIFF(m,sales_date,GETDATE())=0 --this month
GROUP BY customer
ORDER BY SUM(amount) DESC
..rinse and repeat for months 1 thru 11

Anyone have a way to run this in a single statement ?

(#topcust has a unique index for custnbr so this results in a list of customers who have been in the top 10 for any of the past 12 months. This way, we can trend our top customers.)

 

[mimi2]

may be something like:

for i = 1 to 11
INSERT INTO #topcust
SELECT TOP 10 customer FROM REVENUES
WHERE month(sales_date)=i and year(sales_date)= year (getdate())
GROUP BY customer
ORDER BY SUM(amount) DESC

 

[swampBoogie]

INSERT INTO #topcust
select customer from revenues as q
where customer in (
SELECT TOP 10 customer FROM REVENUES
WHERE DATEDIFF(m,sales_date,q.sales_date)=0
Group by customer
ORDER BY SUM(amount) DESC )
where datediff(m,sales_date,getdate()) <= 11