Are the shared departments known in advance or should the query find them?
Are there multiple combinations?
Can there be more than two shared departments?
Is the allocation_percentage the same for all employee sharing the same departments?
Do the percentages always add up to 100?
How many rows are in that table?
And finally, what are you actually trying to do? :-)
Thanks for your help Dieter. I was literally waiting for your reply :)
So basically each department is claiming that they should be responsible for the employee since the employee works for more than one department. So I want to assign each employee to one department each. Now there can be many combinations of the departments.
There can be more than 2 shared departments. The percentages always add up to 100.
The allocation % for the same combination will always remain same.
So the way I was trying to do is -
1. I created a column concatenating all the departments for one employee.
2. Then I am doing a group by on that concatenated column.
3. My problem is - I am unable to allocate employees as per the percentages. Like out of all the employees who took english and math 69.46 % should be assigned to english and 30.53 to math from above example.
You can assign a percentage based on a PERCENT_RANK * 100. If it needs to be statistically random you must use a Derived Table to create a RANDOM(1,2000000000) number to ORDER BY it within the PERCENT_RANK.
Is this related to your other post on recursion?
I was able to get the recursive things working from other post - to get a column with concatenated departments.
I was trying something like this to get the data -
WITH RECURSIVE rec_emps(emp_id, dept_id, ALL_depts)
select distinct emp_id, dept_id, ALL_depts from vt_alloc3 qualify row_number() OVER (PARTITION BY all_depts order by emp_id) = 1
select a.* from (
select distinct vt_alloc3.emp_id, vt_alloc3.dept_id, vt_alloc3.ALL_dept from vt_alloc3 WHERE ALLOC_PCT <> 100
qualify row_number() OVER (PARTITION BY vt_alloc3.ALL_dept, vt_alloc3.dept_id order by vt_alloc3.emp_id) <= (( vt_alloc3.alloc_pct * vt_alloc3.cnt_cust ) /100)
) a where
vt_alloc3.emp_id <> a.emp_id
SELECT * from rec_emps order by emp_id,all_dept