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.
use Northwind
go
select employeeid, customerid, count(orderid) as OrderCount into #EmployeeCustomerOrders
from orders
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.
WITH EmployeeCustomerOrders
ReplyDeleteAS ( SELECT EmployeeID,
CustomerID,
OrderCount = COUNT(orderid)
FROM Orders
GROUP BY EmployeeID,
CustomerID
) ,
CustomerList
AS ( SELECT CustomerId,
OrderCount = COUNT(OrderCount),
CustomerRank = DENSE_RANK() OVER ( ORDER BY COUNT(OrderCount) ASC )
FROM EmployeeCustomerOrders
GROUP BY CustomerId
) ,
EmployeeList
AS ( SELECT EmployeeID,
OrderCount = COUNT(OrderCount),
EmployeeRank = DENSE_RANK() OVER ( ORDER BY COUNT(OrderCount) DESC )
FROM EmployeeCustomerOrders
GROUP BY EmployeeID
)
SELECT cl.CustomerID,
EmployeeId = ( SELECT MAX(EmployeeRank)
FROM EmployeeList
WHERE EmployeeRank <= cl.CustomerRank
)
FROM CustomerList cl
LEFT JOIN EmployeeList el ON cl.CustomerRank = el.EmployeeRank
GROUP BY cl.CustomerID,
cl.CustomerRank,
el.EmployeeRank
ORDER BY EmployeeId
select identity(int) as sl,1 as EmployeeId,customerid,ordercount into #custorders from #EmployeeCustomerOrders order by OrderCount asc
ReplyDeleteselect identity(int) as empsl, avg(employeeid) as Employeeid,count(*) as RecievedCount into #Emporders1 from #EmployeeCustomerOrders group by Employeeid order by RecievedCount desc
select identity(int) as empsl, Employeeid, RecievedCount into #Emporders from #Emporders1
declare @Empid int
declare @avg int
declare @TotalCount int
declare @Loop int
declare @avgloop int
declare @Empcount int
set @avgloop = 1
set @Loop = 1
set @Empcount = 1
set @avg = ((select count(*) from #EmployeeCustomerOrders)/(select count(*) from #Emporders) + 1)
select * into #tempcustorders from #custorders
set @TotalCount = (select count(*) from #EmployeeCustomerOrders)
while(@Loop<=@TotalCount)
begin
if(@avgloop > @avg)
begin
set @Empcount = @Empcount + 1
set @avgloop = 1
update #custorders set EmployeeId = (select EmployeeId from #Emporders where empsl=@Empcount) where sl=@Loop
end
else
begin
set @Empid = (select EmployeeId from #Emporders where empsl=@Empcount)
update #custorders set EmployeeId = (select EmployeeId from #Emporders where empsl=@Empcount) where sl=@Loop
set @avgloop = @avgloop + 1
end
set @Loop = @Loop + 1
end
select * from #custorders
First attempt at using row_number and over in 2005. (assuming rounding evaluation)
ReplyDeleteSELECT employee.ID, customer.ID
FROM (select count(distinct(employeeid)) EmployeBuckets FROM #EmployeeCustomerOrders) Total,
(select ID,Orders, row_number() over (order by Orders) [Rank]
from (select employeeid ID, sum(ordercount) Orders from #EmployeeCustomerOrders group by employeeid)E ) employee,
(select ID,Orders, row_number() over (order by Orders) [Rank]
from (select customerid ID, sum(ordercount) Orders from #EmployeeCustomerOrders group by customerid) C ) customer
WHERE ((customer.[RANK]-1)/(Total.EmployeBuckets+1)) = Total.EmployeBuckets - Employee.[Rank]
DECLARE @Employees int
ReplyDeleteselect @Employees = count(distinct(employeeid)) FROM #EmployeeCustomerOrders
--2005
SELECT employee.employeeid, customer.customerid
FROM (
select employeeid,
(@Employees - (row_number() over (order by sum(Ordercount)))) [Rank]
from #EmployeeCustomerOrders
group by employeeid) employee
JOIN (
select customerid,
(row_number() over (order by sum(Ordercount)) - 1)/(@Employees+1) [Rank]
from #EmployeeCustomerOrders
group by customerid) customer
ON customer.[RANK] = Employee.[Rank]
-- 2000
select employeeid,IDENTITY (int,0,1) [rank]
into #Employees
from #EmployeeCustomerOrders
group by employeeid
order by sum(ordercount) asc
select customerid,IDENTITY (int,0,1) [rank]
into #customers
from #EmployeeCustomerOrders
group by customerid
order by sum(ordercount) asc
SELECT employee.employeeid, customer.customerid
FROM ( select employeeid, @Employees-[rank]-1 [rank] from #employees) employee
JOIN ( Select customerid, ([rank])/(@Employees+1) [rank] from #customers) customer
ON customer.[RANK] = Employee.[Rank]
drop table #employees
drop table #customers
Query plan improvement and 2000 equivalent
DECLARE @Employees int
ReplyDeleteSELECT @Employees = COUNT(DISTINCT(employeeid) ) FROM orders
SELECT employee.employeeid, customer.customerid
FROM (SELECT employeeid, (@Employees - (row_number() OVER (ORDER BY COUNT(Orderid)))) [Rank] FROM orders GROUP BY employeeid) employee
JOIN (SELECT customerid, (row_number() OVER (ORDER BY COUNT(Orderid)) - 1)/(@Employees+1) [Rank] FROM orders GROUP BY customerid) customer
ON customer.[RANK] = Employee.[Rank]
to be unecessarily complete