The random appointment of a place of work and each place should not exceed the number of appointed employees

Question

I am trying to choose unique placements for vacancies / hiring employees in the list of places, all employees are already placed in these places, I try to create a new random placement for them with the condition "where", "the new random location of the employee will not be equal to their home place and random chosen way. Employees with their appointment must be less than or equal. Put the numbers of the wise designation from the table "Places" "

Employee table:

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 table "Places" (PlaceNames with the number of employees - designation wise): -

 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 

etc.

tried using newid () as shown below and to be able to select Employees with RandomPosting 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; 

In addition, I need to limit the random selection based on the designation numbers (in the "Places" table) ... in order to assign each Employee PlaceName (from places) randomly, which is not equal to CurrentPosting and Home (in Employee) and Place a reasonable designation will not be exceed the 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) 

This should try to match employees randomly based on their designation, discarding the same currentPosting and home, and not assign more than what is indicated in each column for designation. However, this may return the same employee for multiple locations, as they may correspond to more than one based on these criteria.

---

EDIT: After you noticed your comment that you don’t need a high-performance single request to solve this problem (which I’m not sure is even possible), and since this seems like a more “one-time” process, I wrote the following code using a cursor and one temporary table to solve your assignment problem:

 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 main idea is that it iterates over employees in a random order and assigns each a random place that meets the criteria of different home and current postings, and also controls the amount that is assigned to each place for each Designation, to ensure that there are no places " redirected to each role.

This fragment does not allow you to actually modify your data. The final SELECT simply returns the suggested assignments. However, you can easily modify it to make real changes to the Employee table.

Answer 2

I assume the limitations are:

• The employee cannot move to the same place in which he / she is located.
• All sites must have at least one employee in each category where the employee is expected.

The most important idea is to realize that you are not looking for a "random" task. You are looking for a rearrangement of positions, provided that everyone moves to another place.

I will describe the answer for managers. You will probably need three queries for each type of employee.

The main idea is the ManagerPositions table. This is a place, a sequential number and a serial number inside a 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 INFORMATION_SCHEMA.columns with the row_number () function to assign a sequence. This is a quick and dirty way to get the sequence in SQL Server - but it’s completely correct if the maximum number you need (that is, the maximum number of managers anywhere) is less than the number of columns in the database. There are other methods for handling a more general case.

The next key idea is to rotate places, not to randomly select them. This uses ideas from arithmetic modulo - add an offset and take the remainder for the total number of positions. The final request 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 

Well, I understand that it is difficult. You have a table for each manager position (not an account, as in your application, the presence of a row for each position is important). There are two ways to determine a position. First in place and counting in place (posseqnum). The second is an incremental row identifier.

Find the current position in the table for each manager. This must be unique because I take into account the number of managers in each place. Then add the offset to the position and assign this place. Having an offset greater than maxseqnum, managers are guaranteed to move to another place (with the exception of unusual boundary cases when more than half of managers have one place).

If all positions of the current manager are filled, this ensures that everyone moves to the next place. Since a random identifier is used for scheduling to assign seqnum, the "next" place is random, not the next identifier or in alphabetical order.

In this decision, there are many employees traveling together in the same new place. You can fix this somewhat by trying values ​​other than "1" in the expression (m.seqnum+maxposseqnum+1) .

I understand that there is a way to change this to prevent a correlation between the current location and the next location. This does the following:

• Randomly assigns seqnum ManagerPosition
• Compare the different offsets in the table, evaluating each by the number of times when the two positions in the table separated by this offset are the same.
• Choose an offset with a minimum rating (which is preferably 0).
• Use this bias in the final agreement.

I do not have enough time to write SQL for this.