SQL – Assign jobs to workers – Alternative to a loop?

In SQL Server 2019, I have a table of unassigned tasks, and another table of workers who are available to work on those tasks. My requirement is to assign the workers evenly over the available tasks by updating the WorkerID column in the Tasks table with the ID of the worker who will do the task.

Both the number of workers and the number of tasks can differ each time the SQL is run.
Either table may have more records than the other.
Task assignment just starts with the first worker in the Workers table and ends when the tasks are all assigned. (There is no randomizing of the workers for purposes of fairness from run to run.)
Either table can have zero records.

Given tables with the following structures, I would like to know how to do these assignments without using a loop. I suspect this can be done through the use of row numbers, rank, or some other such SQL Server magic, but I haven’t been able to figure it out. Thanks!

Tasks table:

JobID    JobName           WorkerID
------------------------------------
23       Carry Groceries   NULL
1234     Drive             NULL
6543     Dig               NULL
234567   Walk              NULL
78       Clean Room        NULL
54       Cook Dinner       NULL
2        Move Logs         NULL
34       Cut Grass         NULL
99       Milk Chickens     NULL

​x
 
JobID    JobName           WorkerID------------------------------------23       Carry Groceries   NULL1234     Drive             NULL6543     Dig               NULL234567   Walk              NULL78       Clean Room        NULL54       Cook Dinner       NULL2        Move Logs         NULL34       Cut Grass         NULL99       Milk Chickens     NULL​

Workers table:

WorkerID   WorkerName
---------------------
67         Larry
42         Sue
10         Peter
45         Steve

 
WorkerID   WorkerName---------------------67         Larry42         Sue10         Peter45         Steve​

Expected results for the Tasks table:

JobID    JobName           WorkerID
--------------------------------------
23       Carry Groceries   67
1234     Drive             42
6543     Dig               10
234567   Walk              45
78       Clean Room        67
54       Cook Dinner       42
2        Move Logs         10
34       Cut Grass         45
99       Milk Chickens     67

 
JobID    JobName           WorkerID--------------------------------------23       Carry Groceries   671234     Drive             426543     Dig               10234567   Walk              4578       Clean Room        6754       Cook Dinner       422        Move Logs         1034       Cut Grass         4599       Milk Chickens     67​

Answer

You can assign a number using window functions and then join. A “round-robin” method is:

with toupdate as (
      select t.*, row_number() over (order by (select null)) as seqnum
      from tasks t
     )
update toupdate
     from toupdate join
          (select w.*,
                  row_number() over (order by (select null)) as seqnum,
                  count(*) over () as cnt
           from workers w
          ) w
          on w.seqnum = ( (toupdate.seqnum - 1) % w.cnt) + 1;

 
with toupdate as (      select t.*, row_number() over (order by (select null)) as seqnum      from tasks t     )update toupdate     from toupdate join          (select w.*,                  row_number() over (order by (select null)) as seqnum,                  count(*) over () as cnt           from workers w          ) w          on w.seqnum = ( (toupdate.seqnum - 1) % w.cnt) + 1;​

Advertisement

Answer