Hi everyone,
Wondering if someone can help me with problem am I facing at work.
To keep it simple, I will just assume three tables (in reality there are more).
I have the following tables:
Employees
Jobs
Employee_Job_Ratings
So, the Employees table holds a list of employees, the Jobs table a list of Jobs and the Employee_Job_Ratings table holds a number which represents how "good" the employee is on a particular job.
I am trying to write a query which will determine how best to assign people to jobs within a team. Ie. what combination gives the best total utilization of skills. So, I could have three jobs and three people like this:
Code:
Job
A
B
C
Person
1
2
3
Ratings
A/1: 4
A/2: 2
A/3: 4
B/1: 3
B/2: 1
B/3: 2
C/1: 2
C/2: 3
C/3: 4
So - as you can see above, I could assign the people to jobs in the following permutations:
Code:
*1*
A - 1
B - 2
C - 3
Total: 9
*2*
A - 1
B - 3
C - 2
Total: 9
*3*
B - 1
C - 2
A - 3
Total: 10
*4*
B - 1
C - 3
A - 2
Total: 9
*5*
C - 1
A - 2
B - 3
Total: 7
*6*
C - 1
A - 3
B - 2
Total: 7
But - I obviously want the best people on each job. It would be silly for me to pick say
5 over
3 because the total score of competency for the whole team is 7 vs 10.
So what I am effectively looking to do is work out all the possible combinations that I can have people on each job, then I can do a SUM of their scores and effectively give a "score" for each combination. This will then allow us to see how best to distribute jobs within a team. Ie, do what I did above but automatically.
The above is a trivial example but in reality we have many teams with many jobs in each team. This is currently done manually however I know it can be done automatically - all the data is in our database so it would be excellent to be able to say to the users "this is your best strategy of job assignments", or at least give them an idea. The people in charge of this planning might not use what the system tells them but it allows them to at least get an idea.
I am fine with doing everything above, apart from getting the permutations into rows, ie, from tables A and B with three rows in each, getting the six possible combinations out. A cartesian join unfortunately doesn't do the job.
A person will only ever been assigned to a single job.
I know it's a tall ask and I apologise for not providing any code I have wrote myself, however it's unfortunately the very first step I'm stuck on - once I have that done then I will hopefully be fine!
It's not as simple as just putting a person with a level 4 on one job because they might be a 3 on another whereas everyone else is a 1 and so they would be better used on that other job (ie on a level 3 job instead of a level 4).
Cheers,
