How to search and group by month and year with MSSQL
- This is a follow up to: How to search and group by year
- There are many ways of searching and doing date calculations.
- Often when people are searching for a whole year for summary views or reports they will be using: between 1-Jan and 31-Jan...
- Microsoft SQL Server, up to including SQL Server 2005, does not support dates data types, only date and time. This means that the date also carries the time, and therefore the search for between 1-Jan and 31-Dec will miss the last day for anything after midnight.
- Microsoft SQL Server 2008 does support date only data types. But it will be long time, before people will use them because of the carry over from previous versions. Only new applications/databases will have them.
- It is simpler to just select for the month of January 2008 than to have to explain to the end user that selecting between 1-Jan-2008 and 1-Feb-2008 will only include the year the month of January 2008 and not include any data for February.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT sales_date,sales_qty,sales_price,emp_short
- FROM sales
- ORDER BY sales_date;
- go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2004-03-06 14:01:01.000 0.00 NULL 000110 2004-10-06 10:01:01.000 0.00 NULL 000310 2005-03-25 14:01:01.000 5.00 11.75 000190 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-11-21 10:01:01.000 9.00 11.75 000010 2005-11-30 10:01:01.000 0.00 11.75 000110 2005-12-06 11:01:01.000 8.00 11.75 000010 2005-12-06 11:01:01.000 8.00 11.75 000190 2005-12-22 10:01:01.000 10.00 11.75 000310 2006-01-06 12:01:01.000 7.00 11.75 000030 2006-01-22 12:01:01.000 7.00 11.75 000310 2006-02-23 13:01:01.000 6.00 11.75 000110 2006-02-28 13:01:01.000 0.00 NULL 000190 2006-03-01 16:01:01.000 3.00 11.75 000010 2006-03-06 14:01:01.000 5.00 11.75 000310 2006-03-19 14:01:01.000 5.00 11.75 000110 2006-03-31 17:01:01.000 0.00 11.75 000190 2006-04-06 15:01:01.000 4.00 11.75 000010 2006-04-20 15:01:01.000 4.00 11.75 000030 2006-06-06 17:01:01.000 0.00 NULL 000110 2006-06-06 17:01:01.000 2.00 11.75 000030 2006-07-06 00:00:00.000 1.00 11.75 000190 2006-07-06 19:01:01.000 1.00 11.75 000010 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-07-06 19:01:01.000 1.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000030 2006-10-29 10:01:01.000 3.00 11.75 000110 2006-10-31 10:01:01.000 10.00 11.75 000110 2007-04-18 00:00:00.000 0.00 11.75 000010 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-07 11:00:00.000 2.00 11.75 000010 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-08 05:00:00.000 0.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 2007-05-08 13:30:00.000 1.00 10.00 000030 2007-05-08 15:00:00.000 1.50 11.75 000010 2007-05-09 14:00:00.000 1.25 10.00 000110 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-11 14:15:00.000 0.00 10.00 000190 2007-05-12 15:00:00.000 0.00 10.00 000190 2007-05-13 16:00:00.000 1.00 10.00 000190 2007-05-14 11:00:00.000 3.00 10.00 000310 2007-05-31 00:00:00.000 5.00 11.75 000030 2007-06-17 17:01:01.000 2.00 11.75 000010 2007-07-06 00:00:00.000 1.00 11.75 000190 2007-11-06 10:01:01.000 9.00 11.75 000030 (57 row(s) affected)
Selecting for the month of February for year 2006 with datepart
- SELECT sales_date,sales_qty,sales_price,emp_short
- FROM sales
- WHERE DATEPART(yyyy,sales_date) = 2006 and
- DATEPART(mm,sales_date) = 2
- ORDER BY sales_date;
- go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2006-02-23 13:01:01.000 6.00 11.75 000110 2006-02-28 13:01:01.000 0.00 NULL 000190 (2 row(s) affected)
Summarizing by month and year with datepart
- SELECT DATEPART(yyyy,sales_date) AS 'Year',
- MAX(DATENAME(m,sales_date)) AS 'Month',
- SUM(COALESCE(sales_qty,0)) AS 'Units sold'
- FROM sales
- GROUP BY DATEPART(yyyy,sales_date),
- DATEPART(mm,sales_date)
- ORDER BY DATEPART(yyyy,sales_date),
- DATEPART(mm,sales_date);
Year Month Units sold ----------- ------------------------------ --------------------------------------- 2004 March 0.00 2004 October 0.00 2005 March 5.00 2005 April 4.00 2005 May 3.00 2005 October 10.00 2005 November 9.00 2005 December 26.00 2006 January 14.00 2006 February 6.00 2006 March 13.00 2006 April 8.00 2006 June 2.00 2006 July 4.00 2006 October 63.00 2007 April 0.00 2007 May 37.25 2007 June 2.00 2007 July 1.00 2007 November 9.00 (20 row(s) affected)
- It's easier to use datepart for grouping than to use a large case statement, store the data to a temporary table and then report from the temporary table.
- Line 2: max(datename(m,sales_date)): datename gives the name of the month and the max is because it is not part of the group by clause see: What to do when not all items in select clause are in the Group By
- Line 3: coalesce(sales_qty,0): Summary operations do not include nulls, so if you need to count/average... these rows would not be included.
- If you have defined an index on the date column, the between will use it.
- The datepart calculation will not use the index, even if it exist. The datepart will do a full table scan. For 5000 rows: no problem, for 50 million rows that's another story.