Round-robin assignment

Question

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

In SQL Server:

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