I have two tables:
Employees:
employee_id field max_amount
3 a 3000
4 a 3000
1 a 1600
2 a 500
4 b 4000
2 b 4000
3 b 1700
ordered by employee, field, amount desc.
Amounts (pol, premia,field):
pol premia field **assign_to_employee**
11 900 a 3
44 1000 a 3
55 1400 a 4
77 500 a 3
88 1300 a 1
22 800 b 4
33 3900 b 2
66 1300 b 4
Assign Stats Table:
employee_id field max_amount true_amount remain
3 a 3000 2400 600
4 a 3000 1400 1600
1 a 1600 1300 300
2 a 500 0 500
4 b 4000 2100 1900
2 b 4000 3900 100
3 b 1700 0 1700
The output : assign_to_employee field (merged to amounts table).
Algoritem wise : The method is to assign pol's to employees until the premia needs to be added to the cumulative_sum is bigger then the max amount per employee listed in the employees table. You always start with the employess with most max amount until you cannot add any other pols to the employee.
I start with the employees with the grater max_amount per field.
I keep doing this until no pols remains to be assign.
Can you help me solve this?
Thank you.