Concatenate Date and Order by Grouping

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

General SQL Server Forums

New to SQL Server Programming

Concatenate Date and Order by Grouping

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-02-28 : 12:13:10

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-28 : 12:33:20

[code]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) [/code]

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-02-28 : 12:58:58

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-01 : 10:48:00

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources