Desperately need ur help!

Database
Enthusiast

Desperately need ur help!

Hi!

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.

Tasks Hierarchy Team
100 1 Null
101 1 Null
102 2 Null
103 5 Null
104 3

Any help is gretaly appreciated.

Thanks!
40 REPLIES

Re: Desperately need ur help!

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.
Enthusiast

Re: Desperately need ur help!

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.

Team Hierarchy Dst_ratio SEQ_NUM
Team-B 1 20 1
Team-R 1 10 2
Team-G 1 20 3
Team-R 2 20 4
Team-Y 2 10 5
Team-O 3 30 6
Team-J 5 70 7

Thanks in advance!

Enthusiast

Re: Desperately need ur help!

Dieter! need ur help!!!
Senior Apprentice

Re: Desperately need ur help!

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?

Dieter
Enthusiast

Re: Desperately need ur help!

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 .

Thanks!

Senior Apprentice

Re: Desperately need ur help!

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

Dieter
Enthusiast

Re: Desperately need ur help!

Hi Dieter!

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.

Senior Apprentice

Re: Desperately need ur help!

It's probably because of integer division in your CNT_tasks calculation, try
SEL cast(COUNT(*) as dec(10,0)) TOT_CNT

Dieter
Enthusiast

Re: Desperately need ur help!

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