Desperately need ur help dieter!!! I have spent 2 days trying this using OLAP functions like ROw_number but does not work out in all cases. and I am not allowed to create a procedure.
This is my lookup table. Team Hierarchy Cnt_Tasks SEQ_NUM Team-B 1 22774 1 Team-R 1 11387 2 Team-G 1 1000 3 Team-R 2 1637 4 Team-Y 2 4092 5 Team-O 3 59071 6 Team-J 5 7114 7
And this one is my actual table where i need to populate the teams as per the tasks. Count_tasks column in the look up table is the number of tasks for each Hierarchy which has to be assigned ot a particular team. Eg. There are 22774 tasks with hierarchy 1 which should get assigned to Team B and 7114 tasks with hierarchy 5 should get assigned to TeamJ.
Hi, I have not understood your question. Can you explain who the TASKS has values 100,101 in actual table?why the TEAM has value NULL?.What is input and what is required output,so we can think about logic properly.
Sure. 100 , 101 etc are different values ofr tasks and they are unique. Bot of these tables are input. The second table has the team column NULL because it needs to be populated with the 'Team' from lookup table. So my output should look like below: Tasks Hierarchy Team 100 1 Team-B 101 1 Team-B 102 2 Team-R 103 5 Team-J 104 3 Team-O So 22774 tasks in this table should get assigned to Team B, 11387 tasks should get assigned to Team-R with hierarchy 1. Now note that Team-R has an hierarchy of 1 as well 2.
I basically have the distibution ratio column in lookup table instead of the Cnt_tasks. So my look up table looks like below. So if we have a total of 50 tasks with hiereachy of 1 , then 20 tasks should get assigned to Team-B, 10 tasks should get assigned to Team-R and 20 tasks should get assigned to Team-G. We can find the total number of tasks from my main table which has all the tasks. And I had the Cnt_tasks column by using counting the tasks and using the distribution ratio.
I lost my harddrive in my MacBook and had to fix it first. The good thing about it, i replaced it with a SSD and now it's increadibly fast :-)
I not sure if i actually understand your problem, your narrative is a bit confusing...
In the hierarchy table there are several teams assigned to a hierarchy. Each team has a cnt_tasks. The sum of all cnt_tasks for hierarchy 1 in your first example is 35.161. Based on that you need to calculate the ratio per team/hierarchy. Then you want to that ratio to assign a proportional number of tasks.
Correct? Are there 35.161 tasks for hierarchy 1 in the tasks table or a different number? Are there any additional rules for assigning tasks to teams? How many rows are in each table?
So releived to see your response Dieter!! Good that its faster now!!!
Yes, there are a total of 35161 tasks assigned for hiereachy 1. But you can ignore that and use the ratio for each task as mentioned in my post above. Given below is the Lookup table with th ratio: Team Hierarchy Dst_ratio Team-B 1 20 Team-R 1 10 Team-G 1 20 Team-R 2 20 Team-Y 2 10 Team-O 3 30 Team-J 5 70 I calculated the cnt_tasks column using the foll : sel team,hierarchy, cast(a.TOT_CNT * B.DIST_RT / sum(B.DIST_RT) over (partition by B.TA_HIER_NUM) as integer) CNT_tasks, from lookuptable B INNER JOIN (Sel count(*) TOT_CNT , hierarchy FROM taskstable C group by hierarchy)A ON a.HIERarchy = B.HIERarchy;
The lookup table is updated by the users, so theer can be any number of rows. And the tasks table also cannot be controlled. But there could be about a million rows in the tasks table .
The simplest approach involves a non-equi join over hierarchy, which might cause a lot of cpu-usage, but it mainly depends on the number of hierarchies. You just have to calculate the ranges of tasks assigned to each team and then join using between start and end.
This should return the expected result set:
SELECT t.task_id, t.hierarchy, l.team FROM ( SELECT task_id, hierarchy, ROW_NUMBER() OVER (PARTITION BY hierarchy ORDER BY task_id) AS rn FROM tasks ) AS t JOIN ( SELECT team, hierarchy, COALESCE( SUM(CNT_tasks) OVER (PARTITION BY hierarchy ORDER BY CNT_tasks ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)+1 ,1) AS start_range, SUM(CNT_tasks) OVER (PARTITION BY hierarchy ORDER BY CNT_tasks ROWS UNBOUNDED PRECEDING) AS end_range FROM ( SEL team,hierarchy, CAST(a.TOT_CNT * B.DIST_RT / SUM(B.DIST_RT) OVER (PARTITION BY B.TA_HIER_NUM) AS INTEGER) CNT_tasks FROM lookuptable B INNER JOIN (SEL COUNT(*) TOT_CNT , hierarchy FROM taskstable C GROUP BY hierarchy)A ON a.HIERarchy = B.HIERarchy ) AS dt
) AS l ON t.hierarchy = l.hierarchy AND t.rn BETWEEN l.start_range AND l.end_range
And the final update is just:
UPDATE tasks FROM (query) AS x SET team = x.team WHERE tasks.task_id = x.task_id
No wonder people call you a genius! :) Thank you for your prompt response and solution !! The SEL query did work the first time around !! It did not assign the teams to around 7 tasks, but I thinks that beciuse of the odd numbers of tasks which is actually resulting in cnt_tasks which is off by 1 or 2 tasks per hierarchy.
Tried but its still missing out 2 records. I think its because of this calculation because the division. CAST(a.TOT_CNT * B.DIST_RT / SUM(B.DIST_RT) OVER (PARTITION BY B.TA_HIER_NUM) AS INTEGER) DST_CNT