SQL Stairstep Query Help Needed

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)

Table 1: Accounts tracks accounts placed for collections.

  • SQL count consecutive days
  • How to encrypt all existing stored procedures of a database
  • MSSQL 2012 : PIVOT typologies and dates using PHP
  • sql server management console doesn't work with multi-byte characters
  • Is timestampdiff() in MySQL equivalent to datediff() in SQL Server?
  • combining resultset of many select queries
  • 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)
    

    Table 2: Trans tracks payments made

    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)
    

    Here’s what the desired output should look like

                                     *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
    

    The first two columns sum Account.Amount grouping by month.

    The last 4 columns sum the Tran.TranAmount, by month, for Accounts placed in the given month of the current row.

    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:

    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) < 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
    

    It’s producing the following output:

    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      ...
    

    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.

    SQL Fiddle: http://www.sqlfiddle.com/#!6/272e5/1/0

    Thanks for any assistance.

    4 Solutions collect form web for “SQL Stairstep Query Help Needed”

    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 Month12
    From 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.00
    
    Select STM.SubmissionMonthStartDate,
           ASM.TotalSubmissionAmount,
           STM.TransMonthStartDate,
           STM.TranSummaryMonthNum,
           TSM.TotalTranAmount
    From   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.TransMonthStartDate
    Order By STM.SubmissionMonthStartDate, STM.TranSummaryMonthNum
    OPTION (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
    

    The following query exactly duplicates the results of your final query in your own answer but takes no more than 1/30th the CPU (or better), plus is a whole lot simpler.

    If I had the time & energy I am sure I could find even more improvements… my gut tells me I might not have to hit the Accounts table so many times. But in any case, it’s a huge improvement and should perform very well even for very large result sets.

    See the SqlFiddle for it.

    WITH L0 AS (SELECT 1 N UNION ALL SELECT 1),
    L1 AS (SELECT 1 N FROM L0, L0 B),
    L2 AS (SELECT 1 N FROM L1, L1 B),
    L3 AS (SELECT 1 N FROM L2, L2 B),
    L4 AS (SELECT 1 N FROM L3, L2 B),
    Nums AS (SELECT N = Row_Number() OVER (ORDER BY (SELECT 1)) FROM L4),
    Anchor AS (
       SELECT MinDate = DateAdd(month, DateDiff(month, '20000101', Min(SubmissionDate)), '20000101')
       FROM dbo.Accounts
    ),
    MNums AS (
       SELECT N
       FROM Nums
       WHERE
          N <= DateDiff(month,
             (SELECT MinDate FROM Anchor),
             (SELECT Max(TranDate) FROM dbo.Trans)
          ) + 1
    ),
    A AS (
       SELECT
          AM.AccountMo,
          Amount = Sum(A.Amount)
       FROM
          dbo.Accounts A
          CROSS APPLY (
             SELECT DateAdd(month, DateDiff(month, '20000101', A.SubmissionDate), '20000101')
          ) AM (AccountMo)
       GROUP BY
          AM.AccountMo
    ), T AS (
       SELECT
          AM.AccountMo,
          TM.TranMo,
          TotalTranAmount = Sum(T.TranAmount)
       FROM
          dbo.Accounts A
          CROSS APPLY (
             SELECT DateAdd(month, DateDiff(month, '20000101', A.SubmissionDate), '20000101')
          ) AM (AccountMo)
          INNER JOIN dbo.Trans  T
             ON A.AccountID = T.AccountID
          CROSS APPLY (
             SELECT DateAdd(month, DateDiff(month, '20000101', T.TranDate), '20000101')
          ) TM (TranMo)
       GROUP BY
          AM.AccountMo,
          TM.TranMo
    )
    SELECT
       SubmissionStartMonth = A.AccountMo,
       TotalSubmissionAmount = A.Amount,
       M.TransMonth,
       TransMonthNum = N.N - 1,
       T.TotalTranAmount
    FROM
       A
       INNER JOIN MNums N
          ON N.N >= DateDiff(month, (SELECT MinDate FROM Anchor), A.AccountMo) + 1
       CROSS APPLY (
          SELECT TransMonth = DateAdd(month, N.N - 1, (SELECT MinDate FROM Anchor))
       ) M
       LEFT JOIN T
          ON A.AccountMo = T.AccountMo
          AND M.TransMonth = T.TranMo
    ORDER BY
       A.AccountMo,
       M.TransMonth;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.