Random Assignment of Employees to Locations

Gmoney

Good day all,

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.

Thanks for looking at my issue.

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

   

EMPLOYEE_ID	FIRST_NAME	LAST_NAME
000001	A	Smith
000002	B	Jones
000003	C	utler
000004	D	Thomas
000005	E	Marsh
000006	F	Ruppert
000007	G	Glenn

 

STRET_ADDRESS	CITY	HEAD_CNT	STATE
737 Any Road	Anywhere	3	USA
50 Some Road	Nowhere	4	USA

sdstuber

WITH employees

     AS (SELECT '000001' employee_id, 'A' first_name, 'Smith' last_name FROM DUAL

         UNION ALL

         SELECT '000002', 'B', 'Jones' FROM DUAL

         UNION ALL

         SELECT '000003', 'C', 'Butler' FROM DUAL

         UNION ALL

         SELECT '000004', 'D', 'Thomas' FROM DUAL

         UNION ALL

         SELECT '000005', 'E', 'Marsh' FROM DUAL

         UNION ALL

         SELECT '000006', 'F', 'Ruppert' FROM DUAL

         UNION ALL

         SELECT '000007', 'G', 'Glenn' FROM DUAL

         ),

     locations

     AS (SELECT '737 Any Road' street_address, 'Anywhere' city, 3 head_cnt, 'USA' state FROM DUAL

         UNION ALL

         SELECT '50 Some Road', 'Nowhere', 4, 'USA' FROM DUAL),

     sorted_employees

     AS (SELECT ROWNUM rn, e.*

           FROM (  SELECT *

                     FROM employees

                 ORDER BY DBMS_RANDOM.VALUE) e),

     sorted_locations

     AS (SELECT l.*,

                NVL(

                    SUM(head_cnt)

                        OVER(ORDER BY rnk ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),

                    0

                )

                    total

           FROM (SELECT l.*, DENSE_RANK() OVER (ORDER BY street_address, city, state) rnk

                   FROM locations l) l)

SELECT *

  FROM sorted_employees e, sorted_locations l

WHERE e.rn > l.total AND e.rn <= l.total + l.head_cnt;

I do make the assumption there are enough employees to distribute across all the locations.
If there are not, then the locations toward the end of the list (as sorted by street,cty,state)  will be left empty while the ones toward the beginning will be filled.
Also, if there are too many employees, the extra employees won't be assigned anywhere.

If you need to handle those situations, then please elaborate as to what the expected results would be for each.

sdstuber

WITH employees

     AS (SELECT '000001' employee_id, 'A' first_name, 'Smith' last_name FROM DUAL

         UNION ALL

         SELECT '000002', 'B', 'Jones' FROM DUAL

         UNION ALL

         SELECT '000003', 'C', 'Butler' FROM DUAL

         UNION ALL

         SELECT '000004', 'D', 'Thomas' FROM DUAL

         UNION ALL

         SELECT '000005', 'E', 'Marsh' FROM DUAL

         UNION ALL

         SELECT '000006', 'F', 'Ruppert' FROM DUAL

         UNION ALL

         SELECT '000007', 'G', 'Glenn' FROM DUAL

         ),

     locations

     AS (SELECT '737 Any Road' street_address, 'Anywhere' city, 3 head_cnt, 'USA' state FROM DUAL

         UNION ALL

         SELECT '50 Some Road', 'Nowhere', 4, 'USA' FROM DUAL),

     sorted_employees

     AS (SELECT ROWNUM rn, e.*

           FROM (  SELECT *

                     FROM employees

                 ORDER BY DBMS_RANDOM.VALUE) e),

     sorted_locations

     AS (SELECT l.*,

                NVL(

                    SUM(head_cnt)

                        OVER(ORDER BY rnk ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),

                    0

                )

                    total

           FROM (SELECT l.*, DENSE_RANK() OVER (ORDER BY street_address, city, state) rnk

                   FROM locations l) l)

SELECT *

  FROM sorted_employees e, sorted_locations l

WHERE e.rn > l.total AND e.rn <= l.total + l.head_cnt;

I do make the assumption there are enough employees to distribute across all the locations.
If there are not, then the locations toward the end of the list (as sorted by street,cty,state)  will be left empty while the ones toward the beginning will be filled.
Also, if there are too many employees, the extra employees won't be assigned anywhere.

If you need to handle those situations, then please elaborate as to what the expected results would be for each.

Gmoney

Thanks for your reply; however, unless I am missing something, this becomes a bit cumbersome when I have 37 locations and 173 employees.

John Thorton

why must the locations be "random"?

1) Assign the first location to the first "X" employees.

2) Assign the next location to the next "y employees

3) go to #2 & repeat until done

sdstuber

You don't use the employees and locations portions of the WITH clause,  I don't have your tables, so those views are to simulate your tables.

The query on your end would look like this...

WITH sorted_employees

     AS (SELECT ROWNUM rn, e.*

           FROM (  SELECT *

                     FROM employees  --- change this your employee table name

                 ORDER BY DBMS_RANDOM.VALUE) e),

     sorted_locations

     AS (SELECT l.*,

                NVL(

                    SUM(head_cnt)

                        OVER(ORDER BY rnk ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),

                    0

                )

                    total

           FROM (SELECT l.*, DENSE_RANK() OVER (ORDER BY street_address, city, state) rnk

                   FROM locations l   --- change this to your location table name
) l)

SELECT *

  FROM sorted_employees e, sorted_locations l

WHERE e.rn > l.total AND e.rn <= l.total + l.head_cnt;

And if you decide you don't need to randomize the employees as mentioned above,

simply change
ORDER BY DBMS_RANDOM.VALUE

to

ORDER BY employee_Id   --- or any other sorting criteria to determin who is "first"

Gmoney

I jumped too soon, and did not pay attention to what you were showing me. My fault for not providing sample data. This works perfectly for my need.

Thank you for your prompt response.

Regards,

Greg

Gmoney

This is being used to assign employees to temporary locations, and thus out of fairness simply selecting the assignments at random.

John Thorton

Gmoney wrote:This is being used to assign employees to temporary locations, and thus out of fairness simply selecting the assignments at random.

IMO, you can't do "random" & ensure that all locations be used a specific number of times.

But this is your parade & if you think the problem has been correctly solved, then my opinion should be ignored.

sdstuber

>> IMO, you can't do "random" & ensure that all locations be used a specific number of times.

If you see a bug in my answer please elaborate. I'm happy to try to fix it.
I think it does what was asked with the assumption I mentioned above; but I'm willing to believe I'm author-blind.

The code I posted does just what you suggested, the only thing special about it is the rule for determing who is "first" is randomized.

So, instead of, sorting by name or id I sort randomly.  Then the first X employees, whomever they are in the random list are assigned to the first location, then the next Y employees from the same random list and so on.

I think the logic is sound; but again, I'm willing to believe I've overlooked something, if you see a flaw, do tell.

mathguy

Here is one way to do it. We need to generate a random ordering of employees (doing so with ROW_NUMBER() ordered by random numbers, generated with the DBMS_RANDOM.VALUE() function in the ORDER BY clause of ROW_NUMBER(). We also generate the right number of rows for each location (as many as its HEAD_COUNT) - we do this with a common technique using a hierarchical query. I assume (STREET_ADDRESS, CITY, STATE) is a unique key for your table (it would be better if there was a LOCATION_ID column in the LOCATIONS table, but I am working with what you posted). I generate ordinal numbers for locations with ROWNUM - no need to use functions like ROW_NUMBER() for this; ROWNUM is assigned anyway, so I am spending no extra time to generate these row numbers, and randomness is assured by the assignment of random ordinals to the employees. Then match employees to locations with a join (a lookup operation).

with      --  Begin sample data (for testing purposes)     employees (employee_id, first_name, last_name ) as (
       select '000001', 'A', 'Smith'   from dual union all
       select '000002', 'B', 'Jones'   from dual union all
       select '000003', 'C', 'Butler'  from dual union all
       select '000004', 'D', 'Thomas'  from dual union all
       select '000005', 'E', 'Marsh'   from dual union all
       select '000006', 'F', 'Ruppert' from dual union all
       select '000007', 'G', 'Glenn'   from dual
     ),
     locations (street_address, city, head_cnt, state ) as (
       select '737 Any Road', 'Anywhere', 3, 'USA' from dual union all
       select '50 Some Road', 'Nowhere' , 4, 'USA' from dual     --  End of sample data (not part of the solution). Query continues below this line.
     ),
     loc_needs ( street_address, city, state, rn ) as (
       select     street_address, city, state, rownum
       from       locations
       connect by level <= head_cnt
              and prior street_address = street_address
              and prior city          = city
              and prior state          = state
              and prior sys_guid() is not null
     ),
     employees_ord ( employee_id, first_name, last_name, rn ) as (
       select employee_id, first_name, last_name,
              row_number() over (order by dbms_random.value())
       from   employees
     )
select e.employee_id, e.first_name, e.last_name,
       l.street_address, l.city, l.state
from   employees_ord e join loc_needs l using (rn)
;EMPLOYEE_ID  FIRST_NAME  LAST_NAME  STREET_ADDRESS  CITY      STATE
-----------  ----------  ---------  --------------  --------  -----
000005       E           Marsh      50 Some Road    Nowhere   USA
000007       G           Glenn      50 Some Road    Nowhere   USA
000006       F           Ruppert    50 Some Road    Nowhere   USA
000002       B           Jones      50 Some Road    Nowhere   USA
000004       D           Thomas    737 Any Road     Anywhere  USA
000001       A           Smith      737 Any Road    Anywhere  USA
000003       C           Butler    737 Any Road     Anywhere  USA

Frank Kulash

Hi,

Here's a way that is basically what Sdstuber suggested in reply #1.  You may find parts of this simpler and clearer, such as replacing ROWNUM with ROW_NUMBER, which doesn't require a sub-query.

WITH    sorted_employees     AS ( SELECT em.*          ,      ROW_NUMBER () OVER (ORDER BY dbms_random.value)     AS rn          FROM   employees  em        ),       sorted_locations     AS ( SELECT lo.*          ,      SUM (head_cnt) OVER (ORDER BY street_address) - head_cnt  AS total          FROM   locations  lo        )SELECT  *  FROM  sorted_employees  e  JOIN  sorted_locations  l  ON  e.rn   BETWEEN l.total + 1                                        AND     l.total + l.head_cnt;

If locations.street_address is not unique, then use whatever is unique where I used street_address above.

What if the number of rows in employees is not exactly the same as the total of locations.head_cnt?  (I think John was asking the same thing in reply #7, and not saying that there was any flaw in Sdstuber's solution.  If head_cnt is 100 on some row in locations, but there are only 7 rows in employees, you can ensure that there will be 100 rows for that location in the result set.  In fact, you can ensure that there won't be.) Depending on your requirements, you may want to use a full outer join, and/or use MOD in the join condition.