Round-robin assignment

Question

Hi,

I have a Customers table and would like to assign a Salesperson to each customer in a round-robin fashion.

Customers  
--CustomerID  
--FName  
--SalespersonID

Salesperson  
--SalespersonID  
--FName  

So, if I have 15 customers and 5 salespeople, I would like the end result to look something like this:

CustomerID -- FName -- SalespersonID  
1 -- A -- 1  
2 -- B -- 2  
3 -- C -- 3  
4 -- D -- 4  
5 -- E -- 5  
6 -- F -- 1  
7 -- G -- 2  
8 -- H -- 3  
9 -- I -- 4  
10 -- J -- 5  
11 -- K -- 1  
12 -- L -- 2  
13 -- M -- 3  
14 -- N -- 4  
15 -- 0 -- 5  

etc...

I've been playing around with this for a bit and am trying to write some SQL to update my Customers table with the appropriate SalespersonID, but am having some trouble getting it to work.

Any ideas are greatly appreciated!

Answer 1

Any particular platform?

In SQL Server 2005 and up, you can use ROW_NUMBER (OVER) to assign row numbers and then use your ROW_NUMBER from the customers modulo the number of salespeople to determine the salesperson ROW_NUMBER.

Answer 2

In SQL Server:

WITH    с AS
        (
        SELECT  *, ROW_NUMBER() OVER ORDER BY (customerID) AS rn
        FROM    customers
        ),
        s AS
        SELECT  *,
                ROW_NUMBER() OVER ORDER BY (customerID) AS rn
        FROM    salesPersons
        )
SELECT  c.*, s.*
FROM    с
JOIN    s
ON      s.rn =
        (с.rn - 1) %
        (
        SELECT  COUNT(*)
        FROM    salesPersons
        ) + 1