SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Group and Order by Month name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tones
Starting Member

United Kingdom
17 Posts

Posted - 06/01/2012 :  06:42:02  Show Profile  Reply with Quote
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!

Edited by - Tones on 06/01/2012 08:57:15

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/01/2012 :  09:27:15  Show Profile  Reply with Quote
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)


CODO ERGO SUM
Go to Top of Page

Tones
Starting Member

United Kingdom
17 Posts

Posted - 06/01/2012 :  11:03:36  Show Profile  Reply with Quote
Thank you. This works well.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/01/2012 :  12:14:54  Show Profile  Reply with Quote
or use DATEPART(mm,tbl_Claims.clearedDate) in ORDER BY of your original suggestion

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

Go to Top of Page

Tones
Starting Member

United Kingdom
17 Posts

Posted - 06/06/2012 :  04:51:56  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/07/2012 :  22:54:30  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000