When you need to summarize transactional data by Month, there are several ways to do it, some better than others. What to ultimately choose depends on your needs.
Techniques to Avoid
1. GROUP BY Month(SomeDate)
-- or -- GROUP BY DatePart(month, SomeDate)
Unless you are constraining the data so that it covers only 1 year, and it will always
cover exactly 1 year, you should never just group by the Month of a date since it only returns a number from 1-12, not an actual "month". Thus, data for January 2006 and January 2007 will be consolidated together into "month 1", which is probably not what you want. Overall, I recommend avoiding grouping only on a month number for these reasons.
2. GROUP BY MonthName(SomeDate)
This has the same problems as grouping on a month number when your data covers more than one year. In addition, you have issues with sorting since "December" will sort before "January". Finally, you are forcing presentation code into your database by returning month names in SQL -- you are much better off letting your front end client decide if it wants to display full month names, or abbreviations, or month numbers, rather than forcing a specific format upon them. I highly recommend to avoid using this technique as well.
3. GROUP BY SUBSTRING(CONVERT(varchar(20), SomeDate, xxx), xxx, xxx)
You can always convert your dates to a string format, then grab some part of that formatted string that contains the month and the year and group on that, right? Yes, you could -- but don't. There is no need to introduce formatting and string conversions into your SQL code; keep it short and simple and focus on working with clean, raw data. When you start working with formatted strings, you end up with sorting and comparing issues, you can't do any kind of calculations or "date math" on your data, and you are no longer returning values that your front end clients can work with natively as dates. Avoid converting and parsing strings in SQL unless you have no other choice -- and you have lots of other choices in this case.
Techniques to Use
4. GROUP BY Year(SomeDate), Month(SomeDate)
If you add a Year into your GROUP BY clause, now you can safely group on a month number as well, and you are guaranteed that your results will never combine multiple years into the same month. Use this if you'd like to keep your month and year values in separate columns. Your front end client can easily format these values as "MM-YYYY" or whatever is needed -- don't try to convert and combine these two values together in SQL, there is simply no need to. Keep your data raw and simple and efficient; remember that it is always shorter and easier to combine and format data than it is to parse and separate data. It is also nice to have the Month column separate from the Year column if you are ultimately going to pivot or cross tab your results, so this approach can be very useful if it meets your needs.
5. GROUP BY dateadd(month, datediff(month, 0, SomeDate),0) <-- Recommended
This technique will "round" your date to the first day of the month; thus, if you GROUP on those dates, you are grouping on months. This is nice because it combines the year and month together into one column for easy sorting and comparing and joining, if necessary. It also keeps your data as a true DATETIME value so that you can format it any way you'd like at your client, without the need to concatenate or convert to a DATETIME to present the months like "Jan-2007" or whatever you need. I strongly recommend this technique for all of these reasons and think you will find that overall it is the most flexible and easiest to work with.
6. GROUP BY datediff(month,0,SomeDate)
Ah -- now here's an abstract one. This formula just returns the "month offset from the base date", or the number number of months between the "base date" of 0 and the transaction's date, as an integer. If you look at the values returned, they will just be numbers that don't have any meaning. But if you group and sort on these numbers, you will be grouping and sorting by month. At the end of the day, if you take this month offset and add it to the "base date" of 0, you'll get back a DATETIME value as of the first day of the corresponding month.
SELECT dateadd(month, x.MonthOffset,0) as [Month], SUM(x.val) as Total
SELECT datediff(month,0, trans.trandate) as MonthOffset, trans.val
GROUP BY MonthOffset
ORDER BY MonthOffset
Notice that we are grouping and ordering by our MonthOffset
calculation, but in the end we simply add that offset to the "base date" of 0 to get the actual DATETIME value of the first day of the corresponding month.
This technique is actually very fast and efficient, and works well if you need to join multiple derived tables that have all been summarized by month, since you are just joining on integers. But, it can be tricky to work with since it is pretty abstract. If none of this makes sense, don't worry -- just avoid this technique unless you feel it will be useful for you.
Grouping by Other Time Periods
Grouping on Years, Weeks or Quarters follows the same basic idea: simply "round" your DateTime to the resolution that you need, and group on either the resulting DateTime value or by the number of units since the base date. For example, to group by quarter, you can write:
GROUP BY dateadd(quarter, datediff(quarter, 0, SomeDate),0)
That returns the starting date of each quarter; for example, 10/12/2007 will return 9/1/2007. To get the ending date of the quarter, you can use:
dateadd(quarter, datediff(quarter, 0, SomeDate) + 1, 0) -1
As with months, you could just group on the number of quarters since the "base date" as well:
GROUP BY datediff(quarter,0,SomeDate)
Overall, the exact same concepts apply.
The method to choose for any situation ultimately depends on what you need, but remember: Keep it short and simple in T-SQL, and always do all of your formatting at your presentation layer where it belongs.