Sorting Months by Month Number.

/* Create a sample test record */

IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

       DROP TABLE #MyHead





 SELECT TOP (1000000)

       SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),

       SomeAmount   = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY)

  INTO #MyHead

  FROM master.sys.all_columns ac1

 CROSS JOIN master.sys.all_columns ac2

;



/* Add a clustered index to the table just to keep it from being a heap

    -- and to help with speed a bit by sorting the data.*/

CREATE CLUSTERED INDEX IX_#MyHead_SomeDateTime

    ON #MyHead (SomeDateTime)

;





/* This doesnt sorts based on the Month Number */



 SELECT [Month] = DATENAME(mm,SomeDateTime),

       Amount  = SUM(SomeAmount)

  FROM #MyHead

 WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'

 GROUP BY DATENAME(mm,SomeDateTime)

;



/* The below sorts based on the Month Number */



 SELECT [Month] = DATENAME(mm,SomeDateTime),

       Amount  = SUM(SomeAmount)

  FROM #MyHead

 WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'

 GROUP BY DATENAME(mm,SomeDateTime)

 ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)

;