Its been a long time since I had used this medium of communication. Without getting into the excuses part of the dormancy I will get on with the question...
Here is the scenario. Say, I am in a business consulting company and I am given a table EmployeeCustomerOrders.
The table can be generated from the orders table in the Northwind database using this query.
select employeeid, customerid, count(orderid) as OrderCount into #EmployeeCustomerOrders
group by employeeID, customerID
order by employeeid, customerid
Here you can see that an employee can process multiple customers and similarly a customer can be processed by multiple employees. The consulting experts had told that, to improve the sales, the following changes have to be made:
- A customer should be tied to only one employee
- An employee can process for multiple customers
- The employee who has created most orders should be mapped to the customers who have made the least orders and vise-versa
For example: say there are 100 customers and 10 employees. The employee with the maximum orders will be mapped to the 10 customers who have made the least orders and the employees with the minimum orders will be mapped to the top 10 customers who gave the maximum orders.
The result will have two columns: EmployeeID, CustomerID
How do I go about getting the query. The solution can be in SQL Server 2000 or 2005.