Строка над разделом для суммирования данных по месяцам

У меня есть некоторые данные, которые мне нужно суммировать за каждый месяц, и мне трудно понять, как получить 12 столбцов (по одному на каждый месяц) с общей суммой за этот месяц.

Пример:

Данные как есть:

GrossAmt    ClaimDate
49764.00    2014-08-21 00:00:00.000
1382.43     2014-08-27 00:00:00.000
602.77      2014-09-02 00:00:00.000
497.04      2014-09-02 00:00:00.000

желаемый результат:

GrossAmt    ClaimDate
51146.43    August
1099.81     September

фактический желаемый результат:

July  August    September
0     51146.43  1099.81

Извините, что не включил это раньше!

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

Это то, что у меня есть до сих пор, но я не уверен, куда идти дальше.

With CTE ([GrossAmt],ClaimMonth)
AS(
SELECT TOP 10000 
       Sum([GrossAmt]) as TotalClaimAmt
      ,CASE WHEN Month([ClaimDate]) = 1 THEN 'January'
            WHEN Month([ClaimDate]) = 2 THEN 'February'
            WHEN Month([ClaimDate]) = 3 THEN 'March'
            WHEN Month([ClaimDate]) = 4 THEN 'April'
            WHEN Month([ClaimDate]) = 5 THEN 'May'
            WHEN Month([ClaimDate]) = 6 THEN 'June'
            WHEN Month([ClaimDate]) = 7 THEN 'July'
            WHEN Month([ClaimDate]) = 8 THEN 'August'
            WHEN Month([ClaimDate]) = 9 THEN 'September'
            WHEN Month([ClaimDate]) = 10 THEN 'October'
            WHEN Month([ClaimDate]) = 11 THEN 'November'
            WHEN Month([ClaimDate]) = 12 THEN 'Decmber'
        End  AS ClaimMonth
  FROM [Database].[dbo].[Table]
    )
    Group BY  [ClaimDate]
    )
  Select * From CTE

Возможно, мне может понадобиться базовый урок о том, как работает Row over partition. Я пытался читать статьи, которые я нашел в Интернете, но ни одна из них не кажется мне понятной. Они слишком продвинуты, и я сразу теряюсь. Также не помогает то, что я не очень хорошо учусь, читая, мне нужно это видеть. в действии и понять, как это работает.

В любом случае спасибо за помощь. Вы, ребята, рок! РЕДАКТИРОВАТЬ:

фактический желаемый результат:

July  August    September
0     51146.43  1099.81

Извините, что не включил это раньше!


sql tsql window-functions
person Brian Cascone    schedule 10.10.2014    source источник
comment
Вы могли бы использовать DATENAME(MONTH,[ClaimDate]) вместо этого неуклюжего выражения CASE   -  person Lamak    schedule 10.10.2014

Ответы (4)


arrow_upward
2
arrow_downward

Вам нужно использовать PIVOT, чтобы получить желаемые результаты

CTE выполняет ежемесячное агрегирование, а PIVOT используется для преобразования строк результатов CTE в горизонтальные столбцы.

;With CTE ([GrossAmt],MonthVal)
AS(
SELECT TOP 10000 
       Sum([GrossAmt]) as GrossAmt,
       DATEADD(MONTH, DATEDIFF(MONTH,0,ClaimDate), 0) as MonthVal
  FROM [Table1]
  Group BY  DATEADD(MONTH, DATEDIFF(MONTH,0,ClaimDate), 0)
)
SELECT * FROM
(Select [GrossAmt], DATENAME(MONTH,MonthVal) as ClaimMonth From CTE) t
PIVOT
( MAX(GrossAmt) for ClaimMonth  in 
  ( [January], [Febrauary], [March], [April], [May], [June], [July],  
    [August],  [September], [October], [November], [December] 

  )
)pvt
person radar    schedule 10.10.2014
comment
2 Отличные ответы, но вы также дали отличное объяснение. Спасибо за объяснение. Теперь это имеет гораздо больше смысла. - person Brian Cascone; 10.10.2014

arrow_upward
0
arrow_downward 

DECLARE @Cols NVARCHAR(MAX);
SELECT @Cols =  STUFF((
                SELECT DISTINCT ', ' +  QUOTENAME(DATENAME(MONTH,ClaimDate))
                FROM TABLENAME
                FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'')
DECLARE @Sql NVARCHAR(MAX);

SET @Sql  = 'SELECT ''GrossAmount'' AS AMOUNT_Sorted_By_DATE, 
' + @Cols +   '
FROM
(SELECT [GrossAmt], DATENAME(MONTH,ClaimDate) as ClaimMonth
FROM TABLENAME GROUP BY MONTH(ClaimDate)) AS SourceTable
PIVOT
(
SUM(GrossAmt)
FOR DATENAME(MONTH,ClaimDate) IN (' + @Cols +   ')
) AS PivotTable'
EXECUTE sp_executesql @Sql

Это приведет только к количеству месяцев, фактически имеющих данные.

person Sandeep K    schedule 10.10.2014

arrow_upward

arrow_downward

person    schedule
comment
Я испортил желаемые результаты. Моя вина. На самом деле я ищу это: фактический Желаемый результат: июль август сентябрь 0 51146,43 1099,81 - person Brian Cascone; 10.10.2014

arrow_upward

arrow_downward

person    schedule
comment
Я не минусовал вас, но единственное, о чем я могу думать, это то, что это идеально отвечает на мой первоначальный вопрос, но я опубликовал редактирование, которое в основном изменило весь вопрос. Вы ответили на первый. Моя вина. Но ваше право на то, что я изначально опубликовал. - person Brian Cascone; 10.10.2014