# Random Assignment of Employees to Locations Oracle SQL

• Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
• PL/SQL Release 11.2.0.1.0 - Production "CORE 11.2.0.1.0 Production"
• TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
• NLSRTL Version 11.2.0.1.0 - Production

I will base this question of the HR Schema for simplicity. I have two tables Employees and Locations. For this example, I have 37 locations and 173 employees.

My locations have a specific number assigned indicating the number of employees that need to be assigned to the location. What I am attempting to do is randomly assign the number of required employees for each location from the employees table.

As such an example of desired results would be:

``````EMPLOYEE_ID FIRST_NAME LAST_NAME STREET_ADDRESS CITY STATE
1              A        Smith     737 Any Road Anywhere USA
2              B         Jones     50 Some Road Nowhere USA
3              C         Butler   737 Any Road Anywhere USA
4              D         Thomas    50 Some Road Nowhere USA
5              E         Marsh    737 Any Road Anywhere USA
6              F         Ruppert   50 Some Road Nowhere USA
7              G         Glenn     50 Some Road Nowhere USA
``````
• What is your question? You may want to include table definitions and some sample data, in addition to your attempted SQL statement. Jul 5, 2017 at 17:11

Use the random function to generate a number. "Number" your locations with slots equal to the number of positions they need. For example, if location A needs 15 and location B 16 then request a random number from 1 to 31 and:

• 1-15 go to A
• 16-31 go to B

Save that result to a table, and then pull all employees assigned per site.

• How do you use the RANDOM function to generate the numbers from 1 to 31, without duplicates and without gaps? (Hint: you can't; that would definitely not be RANDOM.) Jul 5, 2017 at 22:30
• You store them as you generate them and then skip the collisions near the end.
– CalZ
Jul 6, 2017 at 11:28

Your professor should have taught you the mod function. You can use rownumber and mod to come up with a number from 0 to n where n is one less than the number that you divide by. Hence mod(rownum, 37)+1 will give you a random number between 1 and 37. This gives you between 4 and 5 people working at every location. Sounds oddly random. Except it would not be random for a database class. This query should help you to get on track.

``````SELECT 'location_id_'||LPAD(mod(rownum, 10)+1, 3, '0') location_id,
employee_id, first_name, last_name, street_address, city, state
FROM employee
ORDER BY rowid;
``````
• Before you post answers, the expectation is that you read the problem statement carefully. The OP stated very clearly that each location has a specific number of needed employees. You seem to have completely missed that part. Jul 5, 2017 at 22:28
• Perhaps the OP could have shown the table structure for all of the tables or at least explained how someone would know how many people belonged in each location. Perhaps the OP could have given names for each location. The OP didn't do any of that. But I provided a starting point on how to solve the problem. Perhaps you can post your own code. Jul 6, 2017 at 13:56
• Gandolf989 - Just for FYI - I don't have a professor nor a database class. This was for a work issue. At 52 I am still learning. It would probably be best not to be so presumptuous, and perhaps you will find life much more interesting and YOU may even learn something yourself.... Aug 26, 2017 at 0:43
• While it might be a work assignment, on the face of it it looks like something that someone would do in a class. Rowid is somewhat random, therefore it would be a reasonable way to guarantee a random and even distribution. Using a random function could easily put too many people in the same branch. Certainly having a computer program tell you which office to work in regardless of how far it is from your house would make life interesting. Aug 28, 2017 at 13:59