I have a store proc that is creating a Monthly Income Statement
Some of the GL accounts do not have data for each month so this is causing a problem with my closing cost not showing the missing periods.
The table COMPANIES_PERIODS contains all my existing Accounting Periods
The table GLTS_ACCOUNTS contains my transactions
The below store proc is almost working buy my problem is that I need the Account_ID to match up with the missing Period for that account.
How can I get this to work.
GROUP BY Period_Nbr
select g.Account_ID, g.Period_Nbr, g.Debit_Amt, g.Credit_Amt
from GLTS_ACCOUNTS as g
where g.Account_ID = '01-05-000-11125-00-00'
SELECT MM.Period_Nbr, Q.Account_ID, Q.Debit_Amt, Q.Credit_Amt
FROM MissingMonths MM
LEFT JOIN Glt_Transactions Q ON MM.Period_Nbr = Q.Period_Nbr
Period Account Amount
15-09 01-05-000-11125-00-00 0.11 NULL
I need for the Account_ID 01-05-000-11125-00-00 to repeat for all transaction missing periods
The periods with NULL had no transactions for that period.
15-10 NULL NULL NULL
15-11 01-05-000-11125-00-00 0.11 NULL
15-12 01-05-000-11125-00-00 0.11 NULL
15-12 01-05-000-11125-00-00 0.64 NULL
15-13 NULL NULL NULL
15-14 NULL NULL NULL