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

Workers table:

WorkerID   WorkerName
---------------------
67         Larry
42         Sue
10         Peter
45         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

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;

Advertisement

Answer