question

I have two tables: work_est and unassigned_work. work_est contains precalculated records that specify what type of work a worker has to do next. create table work_est (id int identity(1,1) primary key, worker char(1), work char(1) ); unassigned_work contains a list of work that has to be assigned to the same worker and how many times. work should be assigned to the next free worker (based on the lowest id in work_est) and this worker has to complete the Batch of work (cnt times). create table unassigned_work ( id int identity(1,1) primary key, work char(1), -- type of work cnt int -- how many units of work have to be completed? ); for example work_est may be filled with the following data: insert into work_est (est_time, worker, work) values ('A', 'x'), ('B', 'y'), ('C', NULL), ('A', NULL), ('C', NULL), ('B', 'y'), ('A', NULL), ('B', 'y'), ('C', NULL), ('A', NULL), ('B', NULL), ('C', NULL), ('A', NULL), ('B', NULL), ('A', NULL), ('C', NULL); and unassigned_work with these three lines: insert into unassigned_work (work, cnt) values ('u', 2), -- 1. assign work 'u' two times to the next free worker ('v', 3), -- 2. assign work 'v' three time to the next free worker ('w', 1) -- 3. etc. I'm looking now for a query that applies unassigned_work to work_est: for every line in unassigned_work the query should select the next free worker in the work_est table based on the lowest id and then update the table for this worker with the assigned work "cnt" times then continue to the next line of unassigned_work. In the example the result of applying the first line of unassigned_work should be: ('A', 'x'), ('B', 'y'), ('C', 'u'), -- assigned from line 1 of unassigned_work ('A', NULL), ('C', 'u'), -- assigned from line 1 of unassigned_work ('B', 'y'), ('A', NULL), ('B', 'y'), ('C', NULL), ('A', NULL), ('B', NULL), ('C', NULL), ('A', NULL), ('B', NULL), ('A', NULL), ('C', NULL); The result for the second line of unassigned_work should be: ('A', 'x'), ('B', 'y'), ('C', 'u'), ('A', 'v'), -- assignment 1/3 from line 2 of unassigned_work ('C', 'u'), ('B', 'y'), ('A', NULL), ('B', 'y'), ('C', 'v'), -- assignment 2/3 from line 2 of unassigned_work ('A', NULL), ('B', NULL), ('C', 'v'), -- assignment 3/3 from line 2 of unassigned_work ('A', NULL), ('B', NULL), ('A', NULL), ('C', NULL); And finally the result for applying the third line of unassigned_work should be: ('A', 'x'), ('B', 'y'), ('C', 'u'), ('A', 'v'), ('C', 'u'), ('B', 'y'), ('A', 'w'), -- assignment 1/1 from line 3 of unassigned_work ('B', 'y'), ('C', 'v'), ('A', NULL), ('B', NULL), ('C', 'v'), ('A', NULL), ('B', NULL), ('A', NULL), ('C', NULL); I could loop with T-SQL loop through unassigned_work and apply every single line to work_est, but I'm looking for a query that updates work_est at once.