Forum Stats

  • 3,826,058 Users
  • 2,260,592 Discussions
  • 7,896,782 Comments

Discussions

Random Assignment of Employees to Locations

Gmoney
Gmoney Member Posts: 148
edited Aug 25, 2017 8:30PM in SQL & PL/SQL

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_IDFIRST_NAMELAST_NAMESTREET_ADDRESSCITYSTATE
1ASmith737 Any RoadAnywhereUSA
2BJones50 Some RoadNowhereUSA
3CButler737 Any RoadAnywhereUSA
4DThomas50 Some RoadNowhereUSA
5EMarsh737 Any RoadAnywhereUSA
6FRuppert50 Some RoadNowhereUSA
7GGlenn50 Some RoadNowhereUSA

   

EMPLOYEE_IDFIRST_NAMELAST_NAME
000001ASmith
000002BJones
000003Cutler
000004DThomas
000005EMarsh
000006FRuppert
000007GGlenn

 

STRET_ADDRESSCITYHEAD_CNTSTATE
737 Any RoadAnywhere3USA
50 Some RoadNowhere4USA
Tagged:
Frank Kulash

Best Answer

  • sdstuber
    sdstuber Member Posts: 215 Bronze Badge
    edited Jul 5, 2017 12:22PM Answer ✓

    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.

    Frank Kulash
«1

Answers

  • sdstuber
    sdstuber Member Posts: 215 Bronze Badge
    edited Jul 5, 2017 12:22PM Answer ✓

    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.

    Frank Kulash
  • Gmoney
    Gmoney Member Posts: 148
    edited Jul 5, 2017 12:29PM

    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
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 5, 2017 12:35PM

    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
    sdstuber Member Posts: 215 Bronze Badge
    edited Jul 5, 2017 12:45PM

    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
    Gmoney Member Posts: 148
    edited Jul 5, 2017 12:43PM

    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
    Gmoney Member Posts: 148
    edited Jul 5, 2017 12:44PM

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

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 5, 2017 12:49PM
    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
    sdstuber Member Posts: 215 Bronze Badge
    edited Jul 5, 2017 1:01PM

    >> 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
    mathguy Member Posts: 10,539 Blue Diamond
    edited Jul 5, 2017 2:15PM

    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
    Frank Kulash Member, Moderator Posts: 42,047 Red Diamond
    edited Jul 5, 2017 2:27PM

    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.

This discussion has been closed.