SQL Stairstep Query

Since you are using SQL Server 2012, we can use the Format function to make the date pretty. There is no need to group by the strings. Instead, I find it useful to use the proper data type for as long as I can and only use Format or Convert on display (or not at all and let the middle tier handle the display).

In this solution, I arbitrarily assumed the earliest TransDate and extract from it, the first day of that month. However, one could easily replace that expression with a static value of the start date desired and this solution would take that and the next 12 months.

With SubmissionMonths As  (  Select DateAdd(d, -Day(A.SubmissionDate) + 1, A.SubmissionDate) As SubmissionMonth    , A.Amount  From dbo.Accounts As A  )  , TranMonths As  (  Select DateAdd(d, -Day(Min( T.TranDate )) + 1, Min( T.TranDate )) As TranMonth      , 1 As MonthNum  From dbo.Accounts As A    Join dbo.Trans As T      On T.AccountId = A.AccountId    Join SubmissionMonths As M      On A.SubmissionDate >= M.SubmissionMonth        And A.SubmissionDate < DateAdd(m,1,SubmissionMonth)  Union All  Select DateAdd(m, 1, TranMonth), MonthNum + 1  From TranMonths  Where MonthNum < 12  )  , TotalBySubmissionMonth As  (  Select M.SubmissionMonth, Sum( M.Amount ) As Total  From SubmissionMonths As M  Group By M.SubmissionMonth  )Select Format(SMT.SubmissionMonth,'yyyy-MM') As SubmissionMonth, SMT.Total  , Sum( Case When TM.MonthNum = 1 Then T.TranAmount End ) As Month1  , Sum( Case When TM.MonthNum = 2 Then T.TranAmount End ) As Month2  , Sum( Case When TM.MonthNum = 3 Then T.TranAmount End ) As Month3  , Sum( Case When TM.MonthNum = 4 Then T.TranAmount End ) As Month4  , Sum( Case When TM.MonthNum = 5 Then T.TranAmount End ) As Month5  , Sum( Case When TM.MonthNum = 6 Then T.TranAmount End ) As Month6  , Sum( Case When TM.MonthNum = 7 Then T.TranAmount End ) As Month7  , Sum( Case When TM.MonthNum = 8 Then T.TranAmount End ) As Month8  , Sum( Case When TM.MonthNum = 9 Then T.TranAmount End ) As Month9  , Sum( Case When TM.MonthNum = 10 Then T.TranAmount End ) As Month10  , Sum( Case When TM.MonthNum = 11 Then T.TranAmount End ) As Month11  , Sum( Case When TM.MonthNum = 12 Then T.TranAmount End ) As Month12From TotalBySubmissionMonth As SMT  Join dbo.Accounts As A    On A.SubmissionDate >= SMT.SubmissionMonth      And A.SubmissionDate < DateAdd(m,1,SMT.SubmissionMonth)  Join dbo.Trans As T    On T.AccountId = A.AccountId  Join TranMonths As TM    On T.TranDate >= TM.TranMonth      And T.TranDate < DateAdd(m,1,TM.TranMonth)Group By SMT.SubmissionMonth, SMT.Total

SQL Fiddle version

The following query pretty much returns what you want. You need to do the to operations separately. I just join the results together:

 select a.yyyymm, a.Amount,        t201201, t201202, t201203, t201204 from (select LEFT(convert(varchar(255), a.submissiondate, 121), 7) as yyyymm,              SUM(a.Amount) as amount       from Accounts a       group by  LEFT(convert(varchar(255), a.submissiondate, 121), 7)       ) a left outer join      (select LEFT(convert(varchar(255), a.submissiondate, 121), 7) as yyyymm,              sum(case when trans_yyyymm = '2012-01' then tranamount end) as t201201,              sum(case when trans_yyyymm = '2012-02' then tranamount end) as t201202,              sum(case when trans_yyyymm = '2012-03' then tranamount end) as t201203,              sum(case when trans_yyyymm = '2012-04' then tranamount end) as t201204       from Accounts a join            (select t.*, LEFT(convert(varchar(255), t.trandate, 121), 7) as trans_yyyymm             from trans t            ) t            on a.accountid = t.accountid       group by LEFT(convert(varchar(255), a.submissiondate, 121), 7)      ) t      on a.yyyymm = t.yyyymm order by 1

I am getting a NULL where you have a 0.00 in two cells.

Thomas, I used your response as inspiration for the following solution I ended up using.

I first create a SubmissionDate, TranDate cross join skeleton date matrix, that I later use to join on the AccountSummary and TranSummary data.

The resulting query output isn't formatted in columns, per TranDate month. Rather I'm using output in a SQL Server Reporting Services matrix, and using a column grouping, based off the TranSummaryMonthNum column, to get the desired formatted output.

SQL Fiddle version

;WITH     --Generate a list of Dates, from the first SubmissionDate, through today.    --Note: Requires the use of: 'OPTION (MAXRECURSION 0)' to generate a list with more than 100 dates.    CTE_AutoDates AS    ( Select Min(SubmissionDate) as FiscalDate      From Accounts      UNION ALL      SELECT DATEADD(Day, 1, FiscalDate)      FROM CTE_AutoDates      WHERE DATEADD(Day, 1, FiscalDate) <= GetDate()    ),    FiscalDates As    ( SELECT FiscalDate,             DATEFROMPARTS(Year(FiscalDate), Month(FiscalDate), 1) as FiscalMonthStartDate        FROM CTE_AutoDates      --Optionaly filter Fiscal Dates by the last known Math.Max(SubmissionDate, TranDate)      Where FiscalDate <= (Select Max(MaxDate)                           From (Select Max(SubmissionDate) as MaxDate From Accounts                                 Union All                                  Select Max(TranDate) as MaxDate From Trans                                ) as MaxDates                         )    ),    FiscalMonths as    ( SELECT Distinct FiscalMonthStartDate       FROM FiscalDates    ),    --Matrix to store the reporting date groupings for the Account submission and payment periods.    SubmissionAndTranMonths AS    ( Select AM.FiscalMonthStartDate as SubmissionMonthStartDate,             TM.FiscalMonthStartDate as TransMonthStartDate,             DateDiff(Month, (Select Min(FiscalMonthStartDate) From FiscalMonths), TM.FiscalMonthStartDate) as TranSummaryMonthNum      From   FiscalMonths AS AM             Join FiscalMonths AS TM             ON TM.FiscalMonthStartDate >= AM.FiscalMonthStartDate    ),    AccountData as    ( Select A.AccountID,              A.Amount,             FD.FiscalMonthStartDate as SubmissionMonthStartDate      From   Accounts as A           Inner Join FiscalDates as FD             ON A.SubmissionDate = FD.FiscalDate    ),    TranData as    ( Select T.AccountID,             T.TranAmount,             AD.SubmissionMonthStartDate,             FD.FiscalMonthStartDate as TranMonthStartDate      From   Trans as T           Inner Join AccountData as AD             ON T.AccountID = AD.AccountID           Inner Join FiscalDates AS FD             ON T.TranDate = FD.FiscalDate    ),    AccountSummaryByMonth As    ( Select ASM.FiscalMonthStartDate,             Sum(AD.Amount) as TotalSubmissionAmount      From   FiscalMonths as ASM           Inner Join AccountData as AD             ON ASM.FiscalMonthStartDate = AD.SubmissionMonthStartDate      Group By             ASM.FiscalMonthStartDate    ),    TranSummaryByMonth As    ( Select STM.SubmissionMonthStartDate,             STM.TransMonthStartDate,             STM.TranSummaryMonthNum,             Sum(TD.TranAmount) as TotalTranAmount      From   SubmissionAndTranMonths as STM           Inner Join TranData as TD             ON STM.SubmissionMonthStartDate = TD.SubmissionMonthStartDate                AND STM.TransMonthStartDate = TD.TranMonthStartDate      Group By             STM.SubmissionMonthStartDate,             STM.TransMonthStartDate,             STM.TranSummaryMonthNum    )--#Inspect 1--Select * From SubmissionAndTranMonths--OPTION (MAXRECURSION 0)--#Inspect 1 Results--SubmissionMonthStartDate TransMonthStartDate TranSummaryMonthNum--2012-01-01               2012-01-01          0--2012-01-01               2012-02-01          1--2012-01-01               2012-03-01          2--2012-01-01               2012-04-01          3--2012-02-01               2012-02-01          1--2012-02-01               2012-03-01          2--2012-02-01               2012-04-01          3--2012-03-01               2012-03-01          2--2012-03-01               2012-04-01          3--2012-04-01               2012-04-01          3--#Inspect 2--Select * From AccountSummaryByMonth--OPTION (MAXRECURSION 0)--#Inspect 2 Results--FiscalMonthStartDate TotalSubmissionAmount--2012-01-01           2099.00--2012-02-01           350.00--2012-03-01           685.00--#Inspect 3--Select * From TranSummaryByMonth--OPTION (MAXRECURSION 0)--#Inspect 3 Results--SubmissionMonthStartDate TransMonthStartDate TranSummaryMonthNum TotalTranAmount--2012-01-01               2012-01-01          0                   300.00--2012-01-01               2012-02-01          1                   300.00--2012-01-01               2012-03-01          2                   300.00--2012-02-01               2012-02-01          1                   325.00--2012-02-01               2012-04-01          3                   25.00--2012-03-01               2012-03-01          2                   656.00--2012-03-01               2012-04-01          3                   15.00Select STM.SubmissionMonthStartDate,       ASM.TotalSubmissionAmount,       STM.TransMonthStartDate,       STM.TranSummaryMonthNum,       TSM.TotalTranAmountFrom   SubmissionAndTranMonths as STM     Inner Join AccountSummaryByMonth as ASM       ON STM.SubmissionMonthStartDate = ASM.FiscalMonthStartDate     Left Join TranSummaryByMonth AS TSM       ON STM.SubmissionMonthStartDate = TSM.SubmissionMonthStartDate          AND STM.TransMonthStartDate = TSM.TransMonthStartDateOrder By STM.SubmissionMonthStartDate, STM.TranSummaryMonthNumOPTION (MAXRECURSION 0)--#Results--SubmissionMonthStartDate TotalSubmissionAmount TransMonthStartDate TranSummaryMonthNum TotalTranAmount--2012-01-01               2099.00               2012-01-01           0                  300.00--2012-01-01               2099.00               2012-02-01           1                  300.00--2012-01-01               2099.00               2012-03-01           2                  300.00--2012-01-01               2099.00               2012-04-01           3                  NULL--2012-02-01               350.00                2012-02-01           1                  325.00--2012-02-01               350.00                2012-03-01           2                  NULL--2012-02-01               350.00                2012-04-01           3                  25.00--2012-03-01               685.00                2012-03-01           2                  656.00--2012-03-01               685.00                2012-04-01           3                  15.00