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
 General SQL Server Forums
 New to SQL Server Programming
 Concatenate Date and Order by Grouping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ITTrucker
Yak Posting Veteran

USA
52 Posts

Posted - 02/28/2014 :  12:13:10  Show Profile  Reply with Quote
I'm pulling a simple sum grouped by a date that spans more than one year so I have multiple January records in my result set. I concatenated the date to one field, but now how do I sort by the year as well as the month so I don't have all of my January records first:

select SUM(miles) 'Miles', RIGHT('0' + CONVERT(varchar,MONTH(payperiod)),2) + '/' + convert(varchar,YEAR(payperiod)) 'Month/Year'
from paydetail
where payperiod >= '20120101' and payperiod < '20130201'
group by RIGHT('0' + CONVERT(varchar,MONTH(payperiod)),2) + '/' + convert(varchar,YEAR(payperiod))
order by 'Month/Year'

Which returns:
123456 01/2012
234567 01/2013
345678 02/2012

But I want:
123456 01/2012
135790 02/2012

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/28/2014 :  12:33:20  Show Profile  Reply with Quote
select
	SUM(miles) AS Miles, 
	RIGHT('0' + CONVERT(varchar,MONTH(payperiod)),2) + '/' + convert(varchar,YEAR(payperiod)) 'Month/Year'
from 
	@Foo 
where 
	payperiod >= '20120101' and payperiod < '20130201'  
group by 
	YEAR(payperiod),	
	MONTH(payperiod)
ORDER BY
	YEAR(payperiod),	
	MONTH(payperiod)	
Go to Top of Page

ITTrucker
Yak Posting Veteran

USA
52 Posts

Posted - 02/28/2014 :  12:58:58  Show Profile  Reply with Quote
Yup, that worked exactly like it's supposed to. I'm too used to cheating on my group by's and just throwing everything from the select statement in there instead of actually thinking about what's going on.

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/01/2014 :  10:48:00  Show Profile  Reply with Quote
you can simply keep them as date itself while grouping and apply you conversion in select
ie like below

SELECT RIGHT(CONVERT(varchar(11),DATEADD(mm,DATEDIFF(mm,0,payperiod),0),103),7) AS MOnthYear ,
SUM(miles) AS Miles
FROM paydetail 
where payperiod >= '20120101' and payperiod < '20130201' 
GROUP BY DATEDIFF(mm,0,payperiod) 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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