I am trying to compare 2016 budget amounts to 2015 actual amounts by general ledger (G/L) account, Department and Period. However, we do not budget by every G/L account, so if I query all of the rows in 2016, I'd like to append to the query results Dept-Account combinations that existed in 2015, but which we don't have budgeted in 2016.
I thought I could join the original table to Custom SQL results (see SQL below) of the prior year; where I added a field (Next_Year_Period_ID = PERIOD_ID + 12) that is used to join to the original table on Period Id.
PERIOD_ID + 12 AS Next_Year_Period_ID,
ACT_AMT AS "PY Amount"
However, when I join the tables using a Full Outer Join on:
DEPT = DEPT;
ACCOUNT = ACCOUNT;
PERIOD ID = NEXT YEAR PERIOD ID
I only get PY Amounts for Dept-Account-Period ID combinations that have a 2016 budget amount. I do not get any rows for accounts that are not budgeted in 2016.
Any advice on how to approach this problem would be very much appreciated. Thank you - Randy