Use TSQLFundamentals2008;

SELECT orderyear,MAX(numcusts) as maxcustomers
FROM (SELECT empid, year(orderdate) AS orderyear,
             COUNT(DISTINCT custid) AS numcusts 
      FROM Sales.Orders
      GROUP BY empid, YEAR(orderdate)) Scores
GROUP BY orderyear

SELECT 
	 empid
	,DATEPART(YEAR,orderdate) AS orderyear
	,COUNT(DISTINCT custid) AS numcusts 
	,DENSE_RANK() OVER (PARTITION BY DATEPART(YEAR,orderdate) ORDER BY COUNT(DISTINCT custid) DESC/*, empid*/) AS Ranking
INTO #EmpIDRank
FROM Sales.Orders
GROUP BY empid, YEAR(orderdate)

SELECT * FROM #EmpIDRank WHERE Ranking = 1

quote:

---

Originally posted by theboyholty

The best way to approach this (in my opinion), is with the DENSE_RANK command. Even this way there are a number of approaches depending on your business rules.

Take a look at this code:

SELECT 
	 empid
	,DATEPART(YEAR,orderdate) AS orderyear
	,COUNT(DISTINCT custid) AS numcusts 
	,DENSE_RANK() OVER (PARTITION BY DATEPART(YEAR,orderdate) ORDER BY COUNT(DISTINCT custid) DESC/*, empid*/) AS Ranking
INTO #EmpIDRank
FROM Sales.Orders
GROUP BY empid, YEAR(orderdate)

SELECT * FROM #EmpIDRank WHERE Ranking = 1

The DENSE_RANK function uses PARTITION BY which is similar to GROUP BY - I've PARTITIONed BY the order year then ordered it by the DISTINCT COUNT of customers. Note that if two employees have the same figures it will select both. You can add the commented out code from the DENSE_RANK command to select the one with the highest or lowest empid (or use some other criteria) if you only want to return ONE result for each year.

The DENSE_RANK will return a 1 for the empid with the highest distinct customers for the order year. Then you can filter the data into a CTE or temp table WHERE Ranking = 1.

Have a look at the code and have a play with it. You may find, as I have done, that DENSE_RANK is a fantastic command that is incredibly useful.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club
http://www.hollowtreewebdesign.co.uk - a web design company in its infancy

---