SQL View for Period Totals when no Record in Totals Table for Period

Transact-SQL
1

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

2

You need to CROSS JOIN table1 with table 3 - then outer (left) join table2

 Select *
   From @table1         t1
  Cross Join @table3    t3
   Left Join @table2    t2 On t2.Year1 = t1.Year1
                          And t2.PeriodID = t1.PeriodID
                          And t2.AccountNumber = t3.AccountNumber
  Order By
        t1.year1
      , t1.PeriodID;

Once you have this - you can then put that in a CTE - then use an OUTER APPLY to get the previous non-null value for each account.

   With accounts
     As (
 Select t1.Year1
      , t1.PeriodID
      , t3.AccountNumber
      , t2.PeriodBalance
      , t2.DebitAmount
      , t2.CreditAmount
   From @table1         t1
  Cross Join @table3    t3
   Left Join @table2    t2 On t2.Year1 = t1.Year1
                          And t2.PeriodID = t1.PeriodID
                          And t2.AccountNumber = t3.AccountNumber
        )
 Select a.Year1
      , a.PeriodID
      , a.AccountNumber
      , coalesce(a.PeriodBalance, c.PeriodBalance) As PeriodBalance
      , coalesce(a.DebitAmount, c.DebitAmount) As DebitAmount
      , coalesce(a.CreditAmount, c.CreditAmount) As CreditAmount
   From accounts        a
  Outer Apply (Select Top 1
                      *
                 From @table2   tt
                Where tt.year1 = a.year1
                  And tt.PeriodID < a.PeriodID
                  And tt.AccountNumber = a.AccountNumber
                  And tt.PeriodBalance Is Not Null
                Order By
                      tt.year1
                    , tt.PeriodID desc) As c 
  Order By
        a.AccountNumber
      , a.year1
      , a.PeriodID;