Hello, thank you for this space.
I have a problem and I am Junior, I have searched if there is any function or set of functions PL/SQL that can help me but I can't find any IDEA.
The Case: Every day I receive a load that we upload to a table, that load has an empty field that is "assigned employee".
What I have to do is update that field with the ID of the employees by assigning that field to them but equally.
Example: I receive a table called Tasks with 3 fields (Task ID, Task Name, and Assigned Employee), the Assigned Employee field is empty, and this table contains 100 rows.
On the other hand I have a table of employees with 10 records, so I have to divide the 100 rows among 10 employees, I would have to distribute 10 records to each employee. SO FAR I GOT IT SOLVED.
THE PROBLEM: I must take into account the historical tasks that each employee already has assigned and assign more records to the employee who has less and fewer records to the employee who has more.
Example, of the 10 employees, they already have the following tasks assigned:
PETER: 1
JOSEPH: 2
ANTONY: 3
LOUIS: 4
CAROLINE: 5
MARY: 6
SOPHIA: 7
KENNY: 8
DAISY: 9
ROBERT: 10
So, you have 55 tasks already assigned and 100 new records to assign, so each employee should be left with 15.5 records, you would assign them the following:
PETER: 14
JOSEPH: 13
ANTONY: 12
LOUIS: 11
CAROLINE: 10
MARY: 9
SOPHIA: 9
KENNY: 8
DAISY: 7
ROBERT: 6
A solution that would work is that there is a % of distribution, for example:
PEDRO: distributes 14% of the new tasks
JOSE: distributes 13% of the new tasks
ANTONIO: hands out 12% of new tasks
LUIS: distributes 11% of the new tasks
CAROLINA: distributes 10% of new tasks
MARIA: distributes 9% of new tasks
SOFIA: distributes 9% of new tasks
KENNY: hand out 8% of new tasks
MARGARITA: distributes 7% of new tasks
ROBERTO: distributes 6% of the new tasks
It does not matter that the distribution is exact 1 to 1, the important thing is that it remains more or less balanced over time.
As much as I've searched and tried things, I can't find a way to update the employee field of the tasks table with the right employees so that they have the same number (not exactly, but within a range) of tasks over time .