hugo-939487 (11/15/2010)

---

Another variaton, using the MONTH function:

 SELECT [Month] = DATENAME(mm,SomeDateTime),
        Amount  = SUM(SomeAmount)
   FROM #MyHead
  WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
  GROUP BY DATENAME(mm,SomeDateTime), MONTH(SomeDateTime)
  ORDER BY MONTH(SomeDateTime)
;

Ola L Martins-329921 (11/15/2010)

---

Maybe the low ratings is due to the fact that most programmers and db-developers "know" this solution already: The old "sort numbers stored as text as numbers"... ("1" ,"2"..."10", "11" and NOT "1", "10", "11", "2", "3"...).

I like the technique, but you should clarify "any year" is not actually any year, it is a valid year within the sql server time span...

Kristian Ask (11/15/2010)

---

This is the best one, I think. Adding MONTH function won't change the grouping as it's the same and it will still use seek. You can also use DATEPART(mm, SomeDate).

Geoff A (11/15/2010)

---

it really isn't the best one. when you compare Jeff's ORDER BY clause vs the one above, Jeff's is more efficient.

I had to test to confirm because I assumed the CAST would cost more, but it doesn't.....

N.North (11/15/2010)

---

You could go for casting the month numbers to names in the select rather than in the ORDER BY clause, as it allows you to use MONTH in most places, and DATENAME only once, which feels more natural:

 SELECT [Month] = DATENAME(mm, DATEADD(mm, MONTH(SomeDateTime), 0)),
         Amount  = SUM(SomeAmount)   
 FROM #MyHead  
 WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'  
 GROUP BY MONTH(SomeDateTime)  
 ORDER BY MONTH(SomeDateTime)

Geoff A (11/15/2010)

---

it really isn't the best one. when you compare Jeff's ORDER BY clause vs the one above, Jeff's is more efficient.

I had to test to confirm because I assumed the CAST would cost more, but it doesn't.....