[sql] SQL Server Group By Month

I have a table which has this schema

ItemID    UserID    Year    IsPaid    PaymentDate  Amount
1         1         2009    0         2009-11-01  300
2         1         2009    0         2009-12-01  342
3         1         2010    0         2010-01-01  243
4         1         2010    0         2010-02-01  2543
5         1         2010    0         2010-03-01  475

I'm trying to get a query working which shows the totals for each month. So far I've tried DateDiff and nested selects, but neither gives me what I want. This is the closest I have I think:

DECLARE @start [datetime] = 2010/4/1;
SELECT ItemID, IsPaid,
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And DateDiff(m, PaymentDate, @start) = 0 AND UserID = 100) AS "Apr",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =1 AND UserID = 100) AS "May",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =2 AND UserID = 100) AS "Jun", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =3 AND UserID = 100) AS "Jul", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =4  AND UserID = 100) AS "Aug", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =5  AND UserID = 100) AS "Sep", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =6  AND UserID = 100) AS "Oct", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =7 AND UserID = 100) AS "Nov", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =8 AND UserID = 100) AS "Dec", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =9 AND UserID = 100) AS "Jan", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =10 AND UserID = 100) AS "Feb", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =11 AND UserID = 100) AS "Mar" 
FROM LIVE L INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY 
WHERE UserID = 16178 

But I just get nulls when I should be getting values. Am I missing something?

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

Should be...

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

The one you have is dividing 2010 by 4, then by 1, then converting to a date. Which is the 57.5th day from 1900-01-01.

Try SELECT @start after your initialisation to check if this is correct.

Restrict the dimension of the NVARCHAR to 7, supplied to CONVERT to show only "YYYY-MM"

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

Another approach, that doesn't involve adding columns to the result, is to simply zero-out the day component of the date, so 2016-07-13 and 2016-07-16 would both be 2016-07-01 - thus making them equal by month.

If you have a date (not a datetime) value, then you can zero it directly:

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

If you have datetime values, you'll need to use CONVERT to remove the time-of-day portion:

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

If you need to do this frequently, I would probably add a computed column PaymentMonth to the table:

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

It's persisted and stored in the table - so there's really no performance overhead querying it. It's a 4 byte INT value - so the space overhead is minimal, too.

Once you have that, you could simplify your query to be something along the lines of:

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 

I prefer combining DATEADD and DATEDIFF functions like this:

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

Together, these two functions zero-out the date component smaller than the specified datepart (i.e. MONTH in this example).

You can change the datepart bit to YEAR, WEEK, DAY, etc... which is super handy.

Your original SQL query would then look something like this (I can't test it as I don't have your data set, but it should put you on the right track).

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

One more thing: the Month column is typed as a DateTime which is also a nice advantage if you need to further process that data or map it .NET object for example.

Now your query is explicitly looking at only payments for year = 2010, however, I think you meant to have your Jan/Feb/Mar actually represent 2009. If so, you'll need to adjust this a bit for that case. Don't keep requerying the sum values for every column, just the condition of the date difference in months. Put the rest in the WHERE clause.

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