Pivot / crosstab with Multiple rows and join

[masakazuenami]

I would like to combine the following two datasets (SOURCE Dataset 1+ Dataset 2) and create the expected result illustrated below.

Both datasets get updated and dynamically change. I need to show the data points (amounts) in the last 2 years, which also need to change dynamically based on when the query was run.  Regardless of matched data points being present or not in the Dataset 2, I would like to show each period (Jan-Dec) in the chronological order for a 2 years timeframe.

I appreciate your help!

[fmvizcaino · Accepted solution]

Hi @masakazuenami ,

I'm attaching an example for you and I've tried to comment every important step to help you understading.

The whole idea was to build first a 2 year calendar with all GL Account and left join everything based on it.

Best,

Fernando Vizcaino

[masakazuenami]

@fmvizcaino  This is exaclty what I was looking for! Thank you so much!