I have 3 sql tables as follows:
Table1: PeriodId
Table2: AccountSummary
Table3: AccountMaster
Table1 contains the following columns:
Year1, PeriodId, PeriodBeginDate, PeriodEndDate
Table2 contains the following columns:
Year1, PeriodId, AccountNumber, PeriodBalance, DebitAmount, CreditAmount
Table3 contains the following columns
AccountNumber, AccountDescription
Table2 only contains records when the Year/Period and Account actually had activity. For example assuming that we have 12 periods in a year (period = month), Table1 has a record for each month in the year 2017 as follows:
2017, 1, 01/01/2017, 01/31/2017
2017, 2, 02/01/2017, 02/28/2017
2017, 3, 03/01/2017, 03/31/2017
2017, 4, 04/01/2017, 04/30/2017
2017, 5, 05/01/2017, 05/31/2017
etc.
Table2 only has a record for Period2
2017, 2, 1200-00-000, 13234.16, 13234.16, 0
2017, 5, 1200-00-000, -487.22, 0, -487.22
Table3 has the accounts
1200-00-000, Accounts Receivable
1210-00-000, Prepaid Expenses
I'm trying to create a sql view that will display the account 1200-00-000 for every period even if there is no record in Table 2. I need to display the period balance from the row in Table2 for all periods after the record in Table2 until the next record exists for that Account. Below is an example of what I need as my result set:
2017, 1, 1200-00-000, 0, 0, 0
2017, 2, 1200-00-000, 13234.16, 13234.16, 0
2017, 3, 1200-00-000, 13234.16, 13234.16, 0
2017, 4, 1200-00-000, 13234.16, 13234.16, 0
2017, 5, 1200-00-000, -487.22, 0, -487.22
2017, 1, 1210-00-000, 0, 0, 0
2017, 2, 1210-00-000, 0, 0, 0
2017, 3, 1210-00-000, 0, 0, 0
2017, 4, 1210-00-000, 0, 0, 0
2017, 5, 1210-00-000, 0, 0, 0