Hello everyone,
I am working on project management report based on data from JIRA and I have received one request to split working time of "Ad-hoc" tasks partially (aliquotely) based on number of active projects running in time of creation of "Ad-hoc" task.
As it might be pretty difficult to understand, let's see example below:
1. Table FACT_Tasks contains list of all tasks. Ad-hoc tasks can be identified based on field "Description" when the cell contain string "Ad-hoc" or "Ad hoc".
| Task ID | Type | Description | Created date | Spent time | Project ID |
| 100 | Task | Ad-hoc meeting week 2 | 14/01/2023 | 60 | (blank) |
| 200 | Incident | UAT testing | 15/01/2023 | 300 | 1111 |
| 300 | Task | Development of app | 13/01/2023 | 600 | 2222 |
| 400 | Task | Ad-hoc meeting week 3 | 21/01/2023 | 100 | (blank) |
2. Table DIM_Projects containc list of all projects.
| Project ID | Project Name | Status | Release date |
| 1000 | Project A | Released | 16.1.2023 |
| 1111 | Project B | On hold | |
| 2000 | Project C | New | |
| 2222 | Project D | In Progress | |
| 3333 | Project E | In Analysis | |
3. Now to split the working time of "Ad-hoc" tasks, we need to identify how many projects were active in time of task creation. In this example, on 14/01/2023 (Created date) there were 3 active projects.
Following criteria apply:
a. Exclude projects with status: "On hold", "New" or "Canceled"
b. For projects with status "Released", take into consideration Release date. If Release date is on or after Created date, then project is counted as active in time of task creation. If Release date is before Created date, then project is not counted as active as the project got status Released already.
For task Ad-hoc meeting week 2: In time of creation of this task, there were 3 active projects (Projects A, D and E)
For task Ad-hoc meeting week 3: In time of creation of this task, there were 2 active projects (Projects D and E)
4. Expected result is below:
| Task ID | Type | Description | Created date | Spent time | Project ID |
| 100.1 | Task | Ad-hoc meeting week 2 | 14/01/2023 | 20 | 1000 |
| 100.2 | Task | Ad-hoc meeting week 2 | 14/01/2023 | 20 | 2222 |
| 100.3 | Task | Ad-hoc meeting week 2 | 14/01/2023 | 20 | 3333 |
| 200 | Incident | UAT testing | 15/01/2023 | 300 | 1111 |
| 300 | Task | Development of app | 13/01/2023 | 600 | 2222 |
| 400.1 | Task | Ad-hoc meeting week 3 | 21/01/2023 | 50 | 2222 |
| 400.2 | Task | Ad-hoc meeting week 3 | 21/01/2023 | 50 | 3333 |
Any help is much appreciated!
Thank you
IvanS
