Randomly assign work location and each location should not exceed the number of designated employees

Question

I am trying to select unique random posting/recruitment places of employees within a list of places, all the employees are already posted at these places, i am trying to generate a new random posting place for them with "where" condition that "employee new random location will not be equal to their home place and randomnly selected Employees with their designation must be less than or equal to Place wise designation numbers from Places table "

the Employee table is :

EmpNo   EmpName           CurrentPosting    Home        Designation   RandomPosting
1       Mac               Alabama           Missouri      Manager       
2       Peter             California        Montana       Manager       
3       Prasad            Delaware          Nebraska      PO       
4       Kumar             Indiana           Nevada        PO       
5       Roy               Iowa              New Jersey    Clerk       

And so on...

And the Places table (PlaceNames with number of employees - designation wise) is :-

PlaceID  PlaceName      Manager     PO    Clerk
1        Alabama           2        0     1
2        Alaska            1        1     1
3        Arizona           1        0     2
4        Arkansas          2        1     1
5        California        1        1     1
6        Colorado          1        1     2
7        Connecticut       0        2     0

and so on...

tried with with newid() like as below and to be able to select Employees with RandomPosting place names,

WITH cteCrossJoin AS (
SELECT e.*, p.PlaceName AS RandomPosting,
       ROW_NUMBER() OVER(PARTITION BY e.EmpNo ORDER BY NEWID()) AS RowNum
    FROM Employee e
        CROSS JOIN  Place p
    WHERE e.Home <> p.PlaceName
)
SELECT *
FROM cteCrossJoin
WHERE RowNum = 1;

additionally I need to limit the random selection based upon designation numbers(in Places table)... that is to assign each Employee a PlaceName(from Places) randomly which is not equal to CurrentPosting and Home(in Employee) and Place wise designation will not exceed as given numbers.

Thanks in advance.

Answer 1

Maybe something like this:

select C.* from 
(
    select *, ROW_NUMBER() OVER(PARTITION BY P.PlaceID, E.Designation ORDER BY NEWID()) AS RandPosition
        from Place as P cross join Employee E
    where P.PlaceName != E.Home AND P.PlaceName != E.CurrentPosting
) as C
where 
    (C.Designation = 'Manager' AND C.RandPosition <= C.Manager) OR
    (C.Designation = 'PO' AND C.RandPosition <= C.PO) OR
    (C.Designation = 'Clerk' AND C.RandPosition <= C.Clerk)

That should attempt to match employees randomly based on their designation discarding same currentPosting and home, and not assign more than what is specified in each column for the designation. However, this could return the same employee for several places, since they could match more than one based on that criteria.

---

EDIT: After seeing your comment about not having a need for a high performing single query to solve this problem (which I'm not sure is even possible), and since it seems to be more of a "one-off" process that you will be calling, I wrote up the following code using a cursor and one temporary table to solve your problem of assignments:

select *, null NewPlaceID into #Employee from Employee

declare @empNo int
DECLARE emp_cursor CURSOR FOR  
SELECT EmpNo from Employee order by newid()

OPEN emp_cursor   
FETCH NEXT FROM emp_cursor INTO @empNo

WHILE @@FETCH_STATUS = 0   
BEGIN
    update #Employee 
    set NewPlaceID = 
        (
        select top 1 p.PlaceID from Place p 
        where 
            p.PlaceName != #Employee.Home AND 
            p.PlaceName != #Employee.CurrentPosting AND
            (
                CASE #Employee.Designation 
                WHEN 'Manager' THEN p.Manager
                WHEN 'PO' THEN p.PO
                WHEN 'Clerk' THEN p.Clerk
                END
            ) > (select count(*) from #Employee e2 where e2.NewPlaceID = p.PlaceID AND e2.Designation = #Employee.Designation)
        order by newid()
        ) 
    where #Employee.EmpNo = @empNo
    FETCH NEXT FROM emp_cursor INTO @empNo   
END

CLOSE emp_cursor
DEALLOCATE emp_cursor

select e.*, p.PlaceName as RandomPosting from Employee e
inner join #Employee e2 on (e.EmpNo = e2.EmpNo)
inner join Place p on (e2.NewPlaceID = p.PlaceID)

drop table #Employee

The basic idea is, that it iterates over the employees, in random order, and assigns to each one a random Place that meets the criteria of different home and current posting, as well as controlling the amount that get assigned to each place for each Designation to ensure that the locations are not "over-assigned" for each role.

This snippet doesn't actually alter your data though. The final SELECT statement just returns the proposed assignments. However you could very easily alter it to make actual changes to your Employee table accordingly.

Answer 2

I am assuming the constraints are:

• An employee cannot go to the same location s/he is currently at.
• All sites must have at least one employee in each category, where an employee is expected.

The most important idea is to realize that you are not looking for a "random" assignment. You are looking for a permutation of positions, subject to the condition that everyone moves somewhere else.

I am going to describe an answer for managers. You will probably want three queries for each type of employee.

The key idea is a ManagerPositions table. This has a place, a sequential number, and a sequential number within the place. The following is an example:

Araria     1    1
Araria     2    2
Arwal      1    3
Arungabad  1    4

The query creates this table by joining to INFORMATION_SCHEMA.columns with a row_number() function to assign a sequence. This is a quick and dirty way to get a sequence in SQL Server -- but perfectly valid as long as the maximum number you need (that is, the maximum number of managers in any one location) is less than the number of columns in the database. There are other methods to handle the more general case.

The next key idea is to rotate the places, rather than randomly choosing them. This uses ideas from modulo arithmetic -- add an offset and take the remainder over the total number of positions. The final query looks like this:

with ManagerPositions as (
     select p.*,
            row_number() over (order by placerand, posseqnum) as seqnum,
            nums.posseqnum
     from (select p.*, newid() as placerand
           from places p
          ) p join
          (select row_number() over (order by (select NULL)) as posseqnum
           from INFORMATION_SCHEMA.COLUMNS c
          ) nums
          on p.Manager <= nums.posseqnum
    ),
   managers as (
    select e.*, mp.seqnum
    from (select e.*,
                 row_number() over (partition by currentposting order by newid()
                                   ) as posseqnum
          from Employees e              
          where e.Designation = 'Manager'
         ) e join
         ManagerPositions mp
         on e.CurrentPosting = mp.PlaceName and
            e.posseqnum = mp.posseqnum
  )
select m.*, mp.PlaceId, mp.PlaceName
from managers m cross join
     (select max(seqnum) as maxseqnum, max(posseqnum) as maxposseqnum
      from managerPositions mp
     ) const join
     managerPositions mp
     on (m.seqnum+maxposseqnum+1) % maxseqnum + 1 = mp.seqnum

Okay, I realize this is complicated. You have a table for each manager position (not a count as in your statement, having a row for each position is important). There are two ways to identify a position. The first is by place and by the count within the place (posseqnum). The second is by an incremental id on the rows.

Find the current position in the table for each manager. This should be unique, because I'm taking into account the number of managers in each place. Then, add an offset to the position, and assign that place. By having the offset larger than the maxseqnum, the managers is guaranteed to move to another location (except in unusual boundary cases where one location has more than half the managers).

If all current manager positions are filled, then this guarantees that all will move to the next location. Because ManagerPositions uses a random id for assigning seqnum, the "next" place is random, not next by id or alphabetically.

This solution does have many employees traveling together to the same new location. You can fix this somewhat by trying values other than "1" in the expression (m.seqnum+maxposseqnum+1).

I realize that there is a way to modify this, to prevent the correlation between the current place and the next place. This does the following:

1. Assigns the seqnum to ManagerPosition randomly
2. Compare different offsets in the table, rating each by the number of times two positions in the table, separated by that offset, are the same.
3. Choose the offset with the minimum rating (which is preferably 0).
4. Use that offset in the final matching clause.

I don't have enough time right now to write the SQL for this.