February 3, 2011 at 1:58 pm
Hi everyone
I need to sort my months in a SSRS dataset i have put together. I am currently ordering like this:
SELECT Count(Field1) as Count_Of, MONTH(DATE) as New_Date
FROM Dataset1
GROUP BY MONTH(DATE)
ORDER BY MONTH(DATE)
This is ok but the months are sorted from January through to December (integers 1 -12) when in fact i want them to begin from April through to March (fiscal year).
Is there a way to tell SQL to do this very simply in the ORDER BY clause? I treid DATENAME but this came upwith some random ordering of the dates.
Your help would be really appreciated!
Thanks in advance.
BTW
February 3, 2011 at 2:01 pm
How about just moving the date around to work within standard functionality?
Basically, take 3 months off an April date, for sorting purposes only, and make the system think that's january.
So, basically, sort on: MONTH( DATEADD( m, -3, datecolumn)). It blows up any chance of using the index to presort, if memory serves.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 2:05 pm
Whenever I have to deal with this category of situation, I use a calendar table. You can define quarters, fiscal years/months, et al, in one, tied to actual dates. Then it's a simple matter of joining to that table and using it to do your calculations, sorts, folds, spindles, and mutilations, based on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 3, 2011 at 2:17 pm
Cheers Craig - i'll give that a try...
February 3, 2011 at 2:19 pm
I have to deal with this kind of thing at times. You can also put a case statement into your ORDER BY clause such that the order is modified. Something like:
ORDER BY CASE WHEN Month(DATE) = 1 THEN 12 ELSE Month(DATE) - 1 END
Of course you'd have to make it work with your fiscal year periods.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
February 3, 2011 at 2:22 pm
Cheers guys
February 3, 2011 at 2:51 pm
Is there a way to tell SQL to do this very simply in the ORDER BY clause? I treid DATENAME
Look at this very short article(Author: Jeff Moden), with lots of code and exmaples to do what I think you want to do
November 16, 2015 at 9:46 am
Evil Kraig F (2/3/2011)
How about just moving the date around to work within standard functionality?Basically, take 3 months off an April date, for sorting purposes only, and make the system think that's january.
So, basically, sort on: MONTH( DATEADD( m, -3, datecolumn)). It blows up any chance of using the index to presort, if memory serves.
Awesome, that works perfectly!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply