Group and Order by Month name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Group and Order by Month name

Author

Topic

Tones
Starting Member

17 Posts

Posted - 2012-06-01 : 06:42:02

I have wrote a report to count totals of calls cleared in the past 24 months. I have it grouped by month + year, and used this code to select the month + year for each call:

 DATENAME(mm, tbl_Claims.clearedDate) + ' ' + DATENAME(yyyy, tbl_Claims.clearedDate) AS monthYear

Then

ORDER BY monthYear

This works fine, however when it appears on my report it shows as:

April 2011
August 2011
etc..
And not in actual month order. 

This could become slightly more complicated by the fact it will display totals from 24 months back. So for example i need it to display Jan 2011 to December 2012 in order

And apolgies for posting in the wrong forum, d'oh!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-06-01 : 09:27:15

[code]select
DATENAME(mm, dateadd(mm,datediff(mm,0,cc.clearedDate),0) ) + ' ' +
DATENAME(yyyy, dateadd(mm,datediff(mm,0,cc.clearedDate),0) )
AS monthYear,
count(*) as [Cleared Count]
from
tbl_Claims cc
group by
-- First Day of Month
dateadd(mm,datediff(mm,0,cc.clearedDate),0)
order by
dateadd(mm,datediff(mm,0,cc.clearedDate),0)[/code]

CODO ERGO SUM

Tones
Starting Member

17 Posts

Posted - 2012-06-01 : 11:03:36

Thank you. This works well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:14:54

or use DATEPART(mm,tbl_Claims.clearedDate) in ORDER BY of your original suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tones
Starting Member

17 Posts

Posted - 2012-06-06 : 04:51:56

The report has to go back over 2 years though. So i thought ordering that way would have January 2011 then January 2012, February 2011 then February 2012 etc?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 22:54:30

quote:
Originally posted by Tones

The report has to go back over 2 years though. So i thought ordering that way would have January 2011 then January 2012, February 2011 then February 2012 etc?

ideally thats what you'll get if you use first suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources