title: SQL Stairstep Query
body: <p>I need some help producing a MS SQL 2012 query that will match the desired stair-step output.  The rows summarize data by one date range (account submission date month), and the columns summarize it by another date range (payment date month) </p>
  
  <p>Table 1: <strong>Accounts</strong> tracks accounts placed for collections.</p>
  
  <pre><code>CREATE TABLE [dbo].[Accounts](
      [AccountID] [nchar](10) NOT NULL,
      [SubmissionDate] [date] NOT NULL,
      [Amount] [money] NOT NULL,
  CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED (AccountID ASC))
  
  INSERT INTO [dbo].[Accounts] VALUES ('1000', '2012-01-01', 1999.00)
  INSERT INTO [dbo].[Accounts] VALUES ('1001', '2012-01-02', 100.00)
  INSERT INTO [dbo].[Accounts] VALUES ('1002', '2012-02-05', 350.00)
  INSERT INTO [dbo].[Accounts] VALUES ('1003', '2012-03-01', 625.00)
  INSERT INTO [dbo].[Accounts] VALUES ('1004', '2012-03-10', 50.00)
  INSERT INTO [dbo].[Accounts] VALUES ('1005', '2012-03-10', 10.00)
  </code></pre>
  
  <p>Table 2: <strong>Trans</strong> tracks payments made</p>
  
  <pre><code>CREATE TABLE [dbo].[Trans](
      [TranID] [int] IDENTITY(1,1) NOT NULL,
      [AccountID] [nchar](10) NOT NULL,
      [TranDate] [date] NOT NULL,
      [TranAmount] [money] NOT NULL,
  CONSTRAINT [PK_Trans] PRIMARY KEY CLUSTERED (TranID ASC))
  
  INSERT INTO [dbo].[Trans] VALUES (1000, '2012-01-15', 300.00)
  INSERT INTO [dbo].[Trans] VALUES (1000, '2012-02-15', 300.00)
  INSERT INTO [dbo].[Trans] VALUES (1000, '2012-03-15', 300.00)
  INSERT INTO [dbo].[Trans] VALUES (1002, '2012-02-20', 325.00)
  INSERT INTO [dbo].[Trans] VALUES (1002, '2012-04-20', 25.00)
  INSERT INTO [dbo].[Trans] VALUES (1003, '2012-03-24', 625.00)
  INSERT INTO [dbo].[Trans] VALUES (1004, '2012-03-28', 31.00)
  INSERT INTO [dbo].[Trans] VALUES (1004, '2012-04-12', 5.00)
  INSERT INTO [dbo].[Trans] VALUES (1005, '2012-04-08', 7.00)
  INSERT INTO [dbo].[Trans] VALUES (1005, '2012-04-28', 3.00)
  </code></pre>
  
  <p>Here's what the desired output should look like</p>
  
  <pre><code>                                 *Total Payments in Each Month*
  SubmissionYearMonth TotalAmount | 2012-01  2012-02  2012-03  2012-04 
  --------------------------------------------------------------------
  2012-01             2099.00     |  300.00   300.00   300.00     0.00
  2012-02             350.00      |           325.00     0.00    25.00
  2012-03             685.00      |                    656.00    15.00
  </code></pre>
  
  <p>The first two columns sum Account.Amount grouping by month.</p>
  
  <p>The last 4 columns sum the Tran.TranAmount, by month, for Accounts placed in the given month of the current row.</p>
  
  <p>The query I've been working with feel close.  I just don't have the lag correct.
  Here's the query I'm working with thus far:</p>
  
  <pre><code>Select SubmissionYearMonth, 
         TotalAmount,
         pt.[0] AS MonthOld0,
         pt.[1] AS MonthOld1,
         pt.[2] AS MonthOld2,
         pt.[3] AS MonthOld3,
         pt.[4] AS MonthOld4,
         pt.[5] AS MonthOld5,
         pt.[6] AS MonthOld6,
         pt.[7] AS MonthOld7,
         pt.[8] AS MonthOld8,
         pt.[9] AS MonthOld9,
         pt.[10] AS MonthOld10,
         pt.[11] AS MonthOld11,
         pt.[12] AS MonthOld12,
         pt.[13] AS MonthOld13
  
  From (
         SELECT Convert(Char(4),Year(SubmissionDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, SubmissionDate)),2) AS SubmissionYearMonth, 
         SUM(Amount) AS TotalAmount
         FROM   Accounts
         GROUP BY Convert(Char(4),Year(SubmissionDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, SubmissionDate)),2)
       ) 
  AS AccountSummary
  OUTER APPLY
  (
  SELECT *
  FROM (
         SELECT CASE WHEN DATEDIFF(Month, SubmissionDate, TranDate) &lt; 13
                     THEN DATEDIFF(Month, SubmissionDate, TranDate)
                     ELSE 13
                END AS PaymentMonthAge,
                TranAmount
         FROM Trans INNER JOIN Accounts ON Trans.AccountID = Accounts.AccountID
         Where Convert(Char(4),Year(TranDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, TranDate)),2)
               = AccountSummary.SubmissionYearMonth
         ) as TransTemp
         PIVOT (SUM(TranAmount)
                FOR PaymentMonthAge IN ([0],
                                        [1],
                                        [2],
                                        [3],
                                        [4],
                                        [5],
                                        [6],
                                        [7],
                                        [8],
                                        [9],
                                        [10],
                                        [11],
                                        [12],
                                        [13])) as TransPivot
  ) as pt
  </code></pre>
  
  <p>It's producing the following output:</p>
  
  <pre><code>SubmissionYearMonth TotalAmount MonthOld0 MonthOld1 MonthOld2 MonthOld3 ...
  2012-01             2099.00     300.00    NULL      NULL      NULL      ...
  2012-02             350.00      325.00    300.00    NULL      NULL      ...
  2012-03             685.00      656.00    NULL      300.00    NULL      ...
  </code></pre>
  
  <p>As for the column date headers.  I'm not sure what the best option is here.  I could add an additional set of columns and create a calculated value that I could use in the resulting report.</p>
  
  <p>SQL Fiddle: <a href="http://www.sqlfiddle.com/#!6/272e5/1/0" rel="nofollow noreferrer">http://www.sqlfiddle.com/#!6/272e5/1/0</a></p>