Необходимая помощь SQL Stairstep

Ответ 1

Thomas, я использовал ваш ответ как вдохновение для следующего решения, которое я использовал.

Сначала я создаю матрицу дат скелета перекрестного соединения TransmissionDate, TranDate, которую позже использую для соединения с данными AccountSummary и TranSummary.

Полученный результат запроса не отформатирован в столбцах за месяц TranDate. Скорее я использую вывод в базе данных служб отчетов SQL Server и используя группировку столбцов, основанный на столбце TranSummaryMonthNum, для получения желаемого отформатированного вывода.

версия SQL Fiddle

;
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

Ответ 2

Поскольку вы используете SQL Server 2012, мы можем использовать функцию Format, чтобы сделать дату красивой. Нет необходимости группировать строки. Вместо этого я считаю полезным использовать правильный тип данных до тех пор, пока я могу, и использовать только формат или конвертировать на дисплее (или вообще нет, и позволить среднему уровню обрабатывать отображение).

В этом решении я произвольно принял самый ранний TransDate и извлек из него первый день этого месяца. Однако можно легко заменить это выражение статическим значением желаемой даты начала, и это решение займет это и следующие 12 месяцев.

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

Ответ 3

Следующий запрос в значительной степени возвращает то, что вы хотите. Вам нужно выполнять операции отдельно. Я просто присоединяюсь к результатам вместе:

 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

Я получаю NULL, где у вас 0,00 в двух ячейках.

Ответ 4

Следующий запрос точно дублирует результаты вашего окончательного запроса в вашем собственном ответе, но занимает не более 1/30-го процессора (или лучше), плюс проще всего.

Если бы у меня было время и энергия, я уверен, что смогу найти еще больше улучшений... моя кишка говорит мне, что мне не нужно было бы бить по таблице Accounts столько раз. Но в любом случае это огромное улучшение и должно очень хорошо работать даже для очень больших наборов результатов.

Смотрите SqlFiddle для него.

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;