Random Assignment of Employees to Locations Oracle SQL

Question

• 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 

Answer 1

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.

Answer 2

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;