x

How to assign work to a table of precalculated worker slots?

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.

more ▼

asked Aug 13, 2013 at 09:39 PM in Default

avatar image

PeterSawatzki
0 1 1 3

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x367
x170
x38

asked: Aug 13, 2013 at 09:39 PM

Seen: 913 times

Last Updated: Aug 13, 2013 at 09:41 PM

Copyright 2016 Redgate Software. Privacy Policy