SQL Server Group By Month

Question

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

User · Answer

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

User · Answer

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

User · Answer

SELECT CONVERT NVARCHAR 10   PaymentDate  120   Month   SUM Amount   TotalAmount  FROM Payments GROUP BY CONVERT NVARCHAR 10   PaymentDate  120  ORDER BY  Month    You could also try   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server Group By Month

Examples related to sql

Examples related to sql-server-2008

Examples related to datediff