I do have a minor point about Jeff's post. It didn't work for me in SQL 2000, but did work in 2005 and 2008. With SQL 2000 I got the following error message:
Server: Msg 8127, Level 16, State 1, Line 2
Column name '#MyHead.SomeDateTime' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Also, I had to make some modifications to the code for creating the test data to get it to run in SQL 2000.
I'm not saying everything posted has to work in earlier versions of SQL Server, but SQL 2000 is still fairly common, so you should probably note what versions it has been tested with.
Here is another way to do this. Group by the month name and the first day of the month, then use the month name in the select list and order by the first day of the month. Code tested OK in SQL 2000, 2005, 2008, & 2008 R2.
select
[Month] = datename(mm,SomeDateTime),
Amount = sum(SomeAmount)
from
#MyHead
where
SomeDateTime >= '2010' AND SomeDateTime < '2011'
group by
datename(mm,SomeDateTime),
dateadd(mm,datediff(mm,0,SomeDateTime),0)
order by
dateadd(mm,datediff(mm,0,SomeDateTime),0)
Edit:
Here is another method that only groups by a single expression (the first date of the month), and encloses the grouping expression in a MAX to be able to use the DATENAME function. The MAX is neeed for this to work in SQL 2000, but it also works OK in SQL 2005, 2008, and 2008 R2.
select
[Month] = datename(month,max(dateadd(mm,datediff(mm,0,SomeDateTime),0))) ,
Amount = sum(SomeAmount)
from
#MyHead
where
SomeDateTime >= '2010' AND SomeDateTime < '2011'
group by
dateadd(mm,datediff(mm,0,SomeDateTime),0)
order by
dateadd(mm,datediff(mm,0,SomeDateTime),0)
This works OK in SQL 2005, 2008, and 2008 R2 without the MAX. I think it works because SQL Server is smart enough to recognize that the grouping expression is included in the month name expression (guess SQL 2000 wasn't quite as smart).
select
[Month] = datename(month,dateadd(mm,datediff(mm,0,SomeDateTime),0)) ,
Amount = sum(SomeAmount)
from
#MyHead
where
SomeDateTime >= '2010' AND SomeDateTime < '2011'
group by
dateadd(mm,datediff(mm,0,SomeDateTime),0)
order by
dateadd(mm,datediff(mm,0,SomeDateTime),0)