IntervalMatch, linked tables

[RogerG]

I have a the following tables

projects:

project, activity, activitygroup, Astartdate, Afinishdate 

• Activity1 = Stage 1 start
• Activity2 = Stage 2 start and Stage 1 end
• Activity4 = Stage 3 start and Stage 2 end

(the above works fine)

cost:

activity, account, costdate, amount

I am pulling the project, activity and activity group from the projects table

• then getting the earliest activity datestart as the stagedate (activities can be repeated over time)
• then using logic with peek and other if conditions, I extract the Stage Date as the StageStartdate and StageEndDate

(The above works fine)

then 

pulling activity and cost from the costdate

• then left join intervalmatch (costdate) load StageStartdate , StageEndDateresident projects;
• this works ok to pull in the costs, but what the interval match is supposed to define is the stage of the costs go into.

(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