Comments on SQL Garbage Collector: A Scenario to Ponder #12

DECLARE @Employees int
SELECT @Employees = COUN...

2008-06-17T03:43:00.000-04:00

DECLARE @Employees int
SELECT @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

DECLARE @Employees int
select @Employees = coun...

2008-06-17T03:16:00.000-04:00

DECLARE @Employees int
select @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

First attempt at using row_number and over in 2005...

2008-06-16T05:05:00.000-04:00

First attempt at using row_number and over in 2005. (assuming rounding evaluation)

SELECT 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]

select identity(int) as sl,1 as EmployeeId,custome...

2007-11-21T00:23:00.000-05:00

select identity(int) as sl,1 as EmployeeId,customerid,ordercount into #custorders from #EmployeeCustomerOrders order by OrderCount asc
select 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

WITH EmployeeCustomerOrders
AS ( S...

2007-08-03T10:56:00.000-04:00

WITH EmployeeCustomerOrders
AS ( 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

Comments on SQL Garbage Collector: A Scenario to Ponder #12

DECLARE @Employees intSELECT @Employees = COUN...

DECLARE @Employees intselect @Employees = coun...

First attempt at using row_number and over in 2005...

select identity(int) as sl,1 as EmployeeId,custome...

WITH EmployeeCustomerOrders AS ( S...

DECLARE @Employees int
SELECT @Employees = COUN...

DECLARE @Employees int
select @Employees = coun...

WITH EmployeeCustomerOrders
AS ( S...