Sorting Months By Number (SQL Spackle)

Question

Sorting Months By Number (SQL Spackle)

Viewing 15 posts - 16 through 30 (of 97 total)

You must be logged in to reply to this topic. Login to reply

Geoff A SSChampion Points: 11511 More actions · Answer 1

N.North (11/15/2010)
You could go for casting the month numbers to names in the select rather than in the ORDER BY clause, as it allows you to use MONTH in most places, and DATENAME only once, which feels more natural:
SELECT [Month] = DATENAME(mm, DATEADD(mm, MONTH(SomeDateTime), 0)),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime)

but your sort is not correct. it puts Feb first and Jan last.....

Kristian Ask SSC Eights! Points: 907 More actions · Answer 2

Geoff A (11/15/2010)
Kristian Ask (11/15/2010)
hugo-939487 (11/15/2010)
Another variaton, using the MONTH function:
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATENAME(mm,SomeDateTime), MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime)
;
This is the best one, I think. Adding MONTH function won't change the grouping as it's the same and it will still use seek. You can also use DATEPART(mm, SomeDate).
it really isn't the best one. when you compare Jeff's ORDER BY clause vs the one above, Jeff's is more efficient.
I had to test to confirm because I assumed the CAST would cost more, but it doesn't.....

You're quite right!

Dean Cochrane SSC Eights! Points: 992 More actions · Answer 3

I don't know why people would mark this article down, but people are funny sometimes.

Even though I probably won't use this technique (I can't think of a time when I've only had to report on a single contiguous year's data) I think this is a good article for the following reasons:

1. The article does exactly what it sets out to do. It states what the parameters are, then addresses the problem as laid out. This is spackle, as Jeff said, not SQL wallboard or a SQL stud*.

2. It encourages the reader to think about things in a new way. Too often (and I'm guilty of it too) we look at a problem and pressure-fit a solution from our repertoire. An article like this has a little twist in it that, if you're smart, you'll tuck away for one of those days when you're trying to do something new.

* I think most of us aspire to SQL stud-hood.

Robert Dudley SSC Rookie Points: 29 More actions · Answer 4

I made use of the built-in datepart functions available in SQL, as many of you also demonstrated. I used a sub-query to obtain the same results. If you want to select multiple years, in the Where clause you simply use the "in" instead of "=" operator.

SELECT

[Month]

,[Amount] = SUM([Amount])

FROM

(

SELECT

[SortMonth] = MONTH(SomeDateTime)

,[Month] = DATENAME(mm,SomeDateTime)

,[Amount] = SomeAmount

FROM #MyHead

WHERE YEAR(SomeDateTime) = 2010

) a

GROUP BY [Month], [SortMonth]

ORDER BY [SortMonth]

ejoell 66477 Right there with Babe Points: 758 More actions · Answer 5

IE would not permit horizontal scrolling to display either of your queries so I have no idea of what you are proposing. Shame too as I was hoping to send this to a beginning DBA.

sdorris-1150234 SSC-Addicted Points: 457 More actions · Answer 6

All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.

N.North SSC Journeyman Points: 91 More actions · Answer 7

Geoff A (11/15/2010)
N.North (11/15/2010)
You could go for casting the month numbers to names in the select rather than in the ORDER BY clause, as it allows you to use MONTH in most places, and DATENAME only once, which feels more natural:
SELECT [Month] = DATENAME(mm, DATEADD(mm, MONTH(SomeDateTime), 0)),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime)
but your sort is not correct. it puts Feb first and Jan last.....

I really should execute my code before posting it. I think the sort order is correct, but the month names are wrong. Using DATEADD(mm, MONTH(SomeDateTime), -1) should work.

WayneS SSC Guru Points: 95409 More actions · Answer 8

sdorris-1150234 (11/15/2010)
All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.

I completely agree with this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 9

ejoell 66477 (11/15/2010)
IE would not permit horizontal scrolling to display either of your queries so I have no idea of what you are proposing. Shame too as I was hoping to send this to a beginning DBA.

Firefox and Chrome work, not sure what's wrong with IE, but the code can easily be selected and pasted in to SSMS. For a beginner DBA, I'd think you'd want them to practice and test this.

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 10

I'm not sure why someone woud vote this down if they knew this technique. Are you saying that all articles about things "you" know shouldn't be written? I asked Jeff to write this because I see this question regularly, someone assuming a "smart" system that can read the values and sort them by month name.

We need lots of basic articles that help the new people, the people that start working with SQL Server every day, understand little things.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 11

ejoell 66477 (11/15/2010)
IE would not permit horizontal scrolling to display either of your queries so I have no idea of what you are proposing. Shame too as I was hoping to send this to a beginning DBA.

You're screen width must be pretty small as I have to resize my window to be fairly narrow to make the code not display.

Curiosity got the best of me so I tried it in Firefox (I'm an IE8 user). Firefox doesn't have sidescroll either, but unlike IE, it doesn't have greyed-out left and right arrows which suggests sidescrolling exists but isn't functioning.

You may have already known this but highlighting all the text and copy/pasting into SSMS or even Notepad will reveal anything you can't see outside the code box. However, sometimes this will result in some mishandled code positioning, but I haven't seen that problem in a long time.

HTH

Natalya Bankova SSC Journeyman Points: 88 More actions · Answer 12

Same cost, but no predefined values:

SELECT [Month] = DATENAME(mm,SomeDateTime),

Amount = SUM(SomeAmount)

FROM #MyHead

WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'

GROUP BY DATENAME(mm,SomeDateTime), DATEPART(MM, SomeDateTime)

ORDER BY DATEPART(MM, SomeDateTime)

thisisfutile Hall of Fame Points: 3668 More actions · Answer 13

Steve Jones - SSC Editor (11/15/2010)
ejoell 66477 (11/15/2010)
IE would not permit horizontal scrolling to display either of your queries so I have no idea of what you are proposing. Shame too as I was hoping to send this to a beginning DBA.
Firefox and Chrome work, not sure what's wrong with IE, but the code can easily be selected and pasted in to SSMS. For a beginner DBA, I'd think you'd want them to practice and test this.

Steve,

I'm not a Firefox user although I have a version installed. If I resize my window, I can't get a horizontal scroll to appear. Perhaps I'm on an outdated version? (v3.6.12)

UPDATE: OK, I feel silly for posting that as a quick search shows that 3.6.12 is the current build. 😛 Not sure why my horizontal scrolling isn't working though.

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 14

The scrolling is a little strange since it seems to give you scroll bars when you're close to needing them, but don't necessarily need them.

I have 3.6.4 and some windows show the horizontal one and it works, some it doesn't resize the window lower. I'll add a note to look into this.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 15

I do have a minor point about Jeff's post. It didn't work for me in SQL 2000, but did work in 2005 and 2008. With SQL 2000 I got the following error message:

Server: Msg 8127, Level 16, State 1, Line 2

Column name '#MyHead.SomeDateTime' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Also, I had to make some modifications to the code for creating the test data to get it to run in SQL 2000.

I'm not saying everything posted has to work in earlier versions of SQL Server, but SQL 2000 is still fairly common, so you should probably note what versions it has been tested with.

Here is another way to do this. Group by the month name and the first day of the month, then use the month name in the select list and order by the first day of the month. Code tested OK in SQL 2000, 2005, 2008, & 2008 R2.

select

[Month] = datename(mm,SomeDateTime),

Amount = sum(SomeAmount)

from

#MyHead

where

SomeDateTime >= '2010' AND SomeDateTime < '2011'

group by

datename(mm,SomeDateTime),

dateadd(mm,datediff(mm,0,SomeDateTime),0)

order by

dateadd(mm,datediff(mm,0,SomeDateTime),0)

Edit:

Here is another method that only groups by a single expression (the first date of the month), and encloses the grouping expression in a MAX to be able to use the DATENAME function. The MAX is neeed for this to work in SQL 2000, but it also works OK in SQL 2005, 2008, and 2008 R2.

select

[Month] = datename(month,max(dateadd(mm,datediff(mm,0,SomeDateTime),0))) ,

Amount = sum(SomeAmount)

from

#MyHead

where

SomeDateTime >= '2010' AND SomeDateTime < '2011'

group by

dateadd(mm,datediff(mm,0,SomeDateTime),0)

order by

dateadd(mm,datediff(mm,0,SomeDateTime),0)

This works OK in SQL 2005, 2008, and 2008 R2 without the MAX. I think it works because SQL Server is smart enough to recognize that the grouping expression is included in the month name expression (guess SQL 2000 wasn't quite as smart).

select

[Month] = datename(month,dateadd(mm,datediff(mm,0,SomeDateTime),0)) ,

Amount = sum(SomeAmount)

from

#MyHead

where

SomeDateTime >= '2010' AND SomeDateTime < '2011'

group by

dateadd(mm,datediff(mm,0,SomeDateTime),0)

order by

dateadd(mm,datediff(mm,0,SomeDateTime),0)