2

I have two tables: [employee] and [task] and I want to assign 5 tasks from the task table per employee into a tracking table. The assignment needs to be random and each selection should exclude those tasks already selected.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Franke Tokam-Powell
  • 21
  • 1
  • Select the rows of your query [in random order](https://stackoverflow.com/questions/1117761/return-rows-in-random-order). Run the query only once, and use that list in order from top to bottom. – Robert Harvey Dec 27 '18 at 23:16

2 Answers2

0

I think I have found an elegant way of achieve what you want. The only issue is that if you have less Tasks than 5 x Employees, some Employee will be assigned no Tasks at all.

WITH    Employee    AS (SELECT ROW_NUMBER() OVER(ORDER BY NewID()) AS ID, * FROM Employee),
        Task        AS (SELECT ROW_NUMBER() OVER(ORDER BY NewID()) AS ID, * FROM Task)
    SELECT *  
    FROM Employee
    LEFT JOIN Task
        ON Employee.ID = FLOOR((Task.ID-1)/5)+1

You can insert the required results into the Tracking table

As per your comment, added to ensure that the Tasks are proportionally distributed, issue can happen if Tasks > 5 x Employees, the system will assign more tasks to some employees:

WITH    Employee    AS (SELECT ROW_NUMBER() OVER(ORDER BY NewID()) AS ID, * FROM Employee),
            Task        AS (SELECT ROW_NUMBER() OVER(ORDER BY NewID()) AS ID, * FROM Task)
        SELECT *  
        FROM Employee
        LEFT JOIN Task
            ON Employee.ID = FLOOR((Task.ID-1) %
                   (SELECT COUNT(*) FROM Employee)
                  )+1

It uses Modulus % operator to obtain the rest of the integer division so, it gives correlative numbers for every task and when the total number of Employees is reached, starts again from 1.

Angel M.
  • 1,360
  • 8
  • 17
  • I am thinking well and... maybe you need to clarify your requirements as not sure if it is possible that the same Task can be assigned to more than one Employee and the process runs over the time, so, the Tracking table needs to be incremental, so, some additional checks should be added to my reply. – Angel M. Dec 28 '18 at 08:46
  • might not perform well when tables are large as you are creating 2 NEWID()s for each row in each table – Mitch Wheat Dec 28 '18 at 08:47
  • I cannot imagine a company having so many Employees and Tasks that the performance is affected... I see the issue more in the existing data that should be excluded... – Angel M. Dec 28 '18 at 09:06
  • Thanks a lot! This works perfectly except everyone should have an assigned task while this let's some people with no task. could you please explain what this does ?FLOOR((Task.ID-1)/5)+1 why the -1 and +1? – Franke Tokam-Powell Dec 28 '18 at 18:29
  • I have no much time to search for the right function but, FLOOR gets the integer part for the number, so, for Task.ID = 1, 2, 3, 4, 5, 6, 7, 8, 9,10, 11... substracting 1, dividing by 5 obtains 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2... so, you need to search for a function that gives you numbers from 1 to the maximum number of Employees and need to link on that field. – Angel M. Dec 28 '18 at 19:23
0

In this post: Return rows in random order linked by Robert Harvey in a comment you can find a discussion about how inefficient it is to execute queries involving ORDER BY NewID() or even ORDER BY Rand().

In my experience, the best way to accomplish what you want to do is to introduce a new indexed column, say RandomColumn, fill it with random numbers (using Rand()) once, and then each time you want rows at random pick a random number, say R, and select as follows:

... WHERE RandomColumn >= R ORDER BY RandomColumn.

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142