Группа SQL Server по месяцам

Ответ 1

SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120)
ORDER BY [Month]

Вы также можете попробовать:

SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)
ORDER BY Year, Month

Ответ 2

Ограничьте размер NVARCHAR до 7, поставленный в CONVERT, чтобы показывать только "YYYY-MM"

SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(7),PaymentDate,120)
ORDER BY [Month]

Ответ 3

Я предпочитаю комбинировать функции DATEADD и DATEDIFF, как это:

GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0)

Вместе эти две функции обнуляют компонент даты, меньший, чем указанный datepart (т.е. MONTH в этом примере).

Вы можете изменить бит datepart на YEAR, WEEK, DAY и т.д...., который является супер удобно.

Ваш исходный запрос SQL будет выглядеть примерно так (я не могу проверить его, поскольку у меня нет набора данных, но он должен поместить вас на правильный трек).

DECLARE @start [datetime] = '2010-04-01';

SELECT
    ItemID,
    UserID,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0) [Month],
    IsPaid,
    SUM(Amount)
FROM LIVE L
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
AND PaymentDate > @start

Еще одна вещь: столбец MONTH вводится как DateTime, что также является хорошим преимуществом, если вам нужно дополнительно обрабатывать эти данные или сопоставлять их с объектом .NET.

Ответ 4

DECLARE @start [datetime] = 2010/4/1;

Должно быть...

DECLARE @start [datetime] = '2010-04-01';

Тот, который у вас есть, делит 2010 на 4, затем на 1, а затем конвертируется в дату. Это 57,5-й день с 1900-01-01.

Попробуйте SELECT @start после вашей инициализации, чтобы проверить, правильно ли это.

Ответ 5

Если вам нужно делать это часто, я бы добавил в таблицу вычисляемый столбец PaymentMonth:

ALTER TABLE dbo.Payments ADD PaymentMonth AS MONTH(PaymentDate) PERSISTED

Он сохранялся и хранился в таблице, поэтому на него не было запросов на повышение производительности. Это значение 4 байта INT, поэтому слишком низки служебные расходы.

Как только вы это сделаете, вы можете упростить свой запрос, чтобы быть чем-то вроде:

SELECT ItemID, IsPaid,
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 1 AND UserID = 100) AS 'Jan',
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 2 AND UserID = 100) AS 'Feb',
.... and so on .....
FROM LIVE L 
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY 
WHERE UserID = 16178 

Ответ 6

Другим подходом, который не связан с добавлением столбцов к результату, является просто обнуление компонента day даты, поэтому 2016-07-13 и 2016-07-16 будут равны 2016-07-01, что делает их по месяцам.

Если у вас есть значение date (не a datetime), вы можете сразу его обнулить:

SELECT
    DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] ),
    COUNT(*)
FROM
    [Table]
GROUP BY
    DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] )

Если у вас есть значения datetime, вам нужно использовать CONVERT для удаления части времени суток:

SELECT
    DATEADD( day, 1 - DATEPART( day, [Date] ),  CONVERT( date, [Date] ) ),
    COUNT(*)
FROM
    [Table]
GROUP BY
    DATEADD( day, 1 - DATEPART( day, [Date] ),  CONVERT( date, [Date] ) )

Ответ 7

Теперь ваш запрос явно просматривает только платежи за год = 2010, однако, я думаю, вы имели в виду, что ваш янв/февр./март фактически представляет 2009 год. Если это так, вам нужно немного подкорректировать этот случай, Не храните требования к значениям суммы для каждого столбца, просто условие разницы дат в месяцах. Поместите это в предложение WHERE.

SELECT 
      SUM( case when DateDiff(m, PaymentDate, @start) = 0 
           then Amount else 0 end ) AS "Apr",
      SUM( case when DateDiff(m, PaymentDate, @start) = 1 
           then Amount else 0 end ) AS "May",
      SUM( case when DateDiff(m, PaymentDate, @start) = 2 
           then Amount else 0 end ) AS "June",
      SUM( case when DateDiff(m, PaymentDate, @start) = 3 
           then Amount else 0 end ) AS "July",
      SUM( case when DateDiff(m, PaymentDate, @start) = 4 
           then Amount else 0 end ) AS "Aug",
      SUM( case when DateDiff(m, PaymentDate, @start) = 5 
           then Amount else 0 end ) AS "Sep",
      SUM( case when DateDiff(m, PaymentDate, @start) = 6 
           then Amount else 0 end ) AS "Oct",
      SUM( case when DateDiff(m, PaymentDate, @start) = 7 
           then Amount else 0 end ) AS "Nov",
      SUM( case when DateDiff(m, PaymentDate, @start) = 8 
           then Amount else 0 end ) AS "Dec",
      SUM( case when DateDiff(m, PaymentDate, @start) = 9 
           then Amount else 0 end ) AS "Jan",
      SUM( case when DateDiff(m, PaymentDate, @start) = 10 
           then Amount else 0 end ) AS "Feb",
      SUM( case when DateDiff(m, PaymentDate, @start) = 11 
           then Amount else 0 end ) AS "Mar"
   FROM 
      Payments I
         JOIN Live L
            on I.LiveID = L.Record_Key
   WHERE 
          Year = 2010 
      AND UserID = 100