Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a the following tables
projects:
project, activity, activitygroup, Astartdate, Afinishdate
(the above works fine)
cost:
activity, account, costdate, amount
I am pulling the project, activity and activity group from the projects table
(The above works fine)
then
pulling activity and cost from the costdate
(here, I am having my costs replicated across the stages or accorss the activity groups when I try to create a "Outer join" type table)
I am trying to generate a pivot table as below. even if there is a row with 0 costs, it still needs to be present
project, activitygroup,stage,Stage, amount (by year)
Project 1 | ActivtyGroup1 | Stage1 | 2020 | 2021 | 2022 |
Project 1 | ActivtyGroup1 | Stage1 | 0 | 0 | 0 |
Project 1 | ActivtyGroup1 | Stage2 | 1 | 0 | 0 |
Project 1 | ActivtyGroup1 | Stage3 | 0 | 1 | 1 |
Project 1 | ActivtyGroup2 | Stage1 | 0 | 0 | 0 |
Project 1 | ActivtyGroup2 | Stage2 | 0 | 0 | 0 |
Project 1 | ActivtyGroup2 | Stage3 | 0 | 0 | 0 |
Project 2 | ActivtyGroup3 | Stage1 | 0 | 1 | 1 |
Project 2 | ActivtyGroup3 | Stage2 | 0 | 0 | 1 |
Project 2 | ActivtyGroup3 | Stage3 | 0 | 0 | 0 |
Project 2 | ActivtyGroup4 | Stage1 | 0 | 0 | 0 |
Project 2 | ActivtyGroup4 | Stage2 | 0 | 0 | 0 |
Project 2 | ActivtyGroup4 | Stage3 | 0 | 0 | 0 |