Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to get the accumulated sum of Task duration. Basically summing up all tasks duration in the task thread. This needs to be done in script.
Any idea's how i can solve this?
Task tree:
level 1 | level 2 | level 3 | Level 4 |
1 | |||
2 | |||
3 | |||
4 | |||
5 | |||
6 | |||
8 | |||
7 |
From this:
RootTaskID | TaskID | TriggeredByTask | duration |
1 | 1 | 2:00 | |
1 | 2 | 1 | 5:00 |
1 | 3 | 1 | 6:00 |
1 | 4 | 1 | 7:00 |
1 | 5 | 4 | 8:36 |
1 | 6 | 4 | 3:45 |
1 | 7 | 1 | 6:07 |
1 | 8 | 6 | 3:28 |
I should get this:
RootTaskID | TaskID | Accum. duration |
1 | 1 | 0:00 |
1 | 2 | 2:00 |
1 | 3 | 2:00 |
1 | 4 | 2:00 |
1 | 5 | 9:00 |
1 | 6 | 9:00 |
1 | 7 | 2:00 |
1 | 8 | 12:45 |
As I see, you have a hierarchy of tasks.
Use hierarchy load to create table view of your tree.
There is a plenty of information hierarchy load in the community.
yes. I've already did that. Mine interest, however, is how to sum to recursively sum up all dependent task in the thread.
Is Accum. duration for each task calculated correctly in example ?
It should be correct. we're summing up all preceding tasks in the thread.
so for Task 8
Task 1 2:00 +
Task 4 7:00 +
Task 6 3:45 =
total 12:45
It's only an idea, but I think you can use it:
RootTaskID | TaskID | TriggeredByTask | duration | step 1 | step 2 | step 3 | result |
1 | 1 | - | 2:00 | - | - | - | 0:00 |
1 | 2 | 1 | 5:00 | 2:00 | - | - | 2:00 |
1 | 3 | 1 | 6:00 | 2:00 | - | - | 2:00 |
1 | 4 | 1 | 7:00 | 2:00 | - | - | 2:00 |
1 | 5 | 4 | 8:36 | 7:00 | 2:00 | - | 9:00 |
1 | 6 | 4 | 3:45 | 7:00 | 2:00 | - | 9:00 |
1 | 7 | 1 | 6:07 | 2:00 | - | - | 2:00 |
1 | 8 | 6 | 3:28 | 3:45 | 7:00 | 2:00 | 12:45 |
Every step you Left joins TaskID and duration so that TaskID=TriggeredByTask.
So on the fist step you get step1 column.
Then you join values from step1 column with the same rules and get step2 etc.
The result is the sum of those stepN columns.
Nice. But how would I know what StepN is? Task tree depth and structure is dynamic.
'While loop' can help you.
As a condition you can chek if there are any values in TriggeredByTask exists to perform a join.
For example in this sample considering the fourth step you should join a table:
TaskID, TriggeredByTask, step3
8, 6, 2:00
But you see that there is no '8's in the TriggeredByTask field. So there is nothing to join.
You can try to perform this comparison in general case.