Sorting Months By Number (SQL Spackle)

Question

Sorting Months By Number (SQL Spackle)

Viewing 15 posts - 46 through 60 (of 97 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Grant Fritchey (11/15/2010)
Sweet. I like it. I like the format and I think it's perfect that it was lead out of the gate by Jeff. Nice, direct and to the point solution. Thanks for posting it.

Heh... remember what you said about writing a book? Now I know what you mean. 🙂

The real credit for these short and direct "SQL Spackle" articles goes to Steve Jones. He's the one that wanted things that fills "small cracks in the SQL wall" so the newbies wouldn't have to read a 20 page article to learn something new.

Thanks for stopping by, ol' friend.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 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.....

Thanks for stopping by, Geoff. I like it a lot when other people test so it doesn't look so much like the mouse guarding the cheese. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Dean Cochrane (11/15/2010)
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.

Thanks, Dean. I was beginning to think that way to many people missed the whole point of these "SQL Spackle" articles. You hit the nail on the head with your commentary above. Thanks for taking the time to post it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Dugi (11/15/2010)
sdorris-1150234 (11/15/2010)
All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.
I vote for this ... Jeff, thanks for the nice article!
:hehe:

Ah, thank you ol' friend. Thanks for stopping by.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

manub22 (11/15/2010)
How about this:
select datename(M,convert(varchar,months)+'/01/2010') as days
from (select 1 as months union
select 2 as months union
select 3 as months union
select 4 as months union
select 5 as months union
select 6 as months union
select 7 as months union
select 8 as months union
select 9 as months union
select 10 as months union
select 11 as months union
select 12 as months ) as M
order by convert(datetime,convert(varchar,months)+'/01/2010')

Thanks for stopping by, Manub. That's a fair idea of how to make a "memory only" table of month names. Let's see you apply it to the #MyHead table of data, now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

Michael Valentine Jones (11/15/2010)
There have been a lot of alternative suggestions posted, so maybe it’s time for a performance test… 🙂
FYI: I never vote on anything, so don't blame me for the rating.

Heh.... not to worry, Michael. If I got a bad rating from you, I'd probably deserve it and I know you'd explain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

Ola L Martins-329921 (11/15/2010)
Steve Jones - SSC Editor (11/15/2010)
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.
I don't know either, and I didn't vote it down, it was a suggestion to Jeff's "why?". And no, I do absolutely not think that articles that I think state the obvious (which Jeff's article didn't) shouldn't be written! I suggested, starting with "Maybe...".
The more I think of the solution, the more I see a beginner's starting point of non-standard-solution thinking pattern to common problems, or the more commonly used "outside-the-box"... Which is good!

Just to be clear, I didn't take it as a slight at all. The clarification probably has Steve breathing a bit easier, though. 😛 I also very much appreciate the "outside-the-box" comment. Thanks, Ola.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

Robert Dudley (11/15/2010)
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]

Good attempt at a thing that Peter Larsson refers to as "preaggregation". My recommendation would be to change the WHERE clause so it can use the index (ie: Index Seek) on the SomeDateTime column to significantly speed it up. Like this...

Hmmmm... could be another "SQL Spackle" article there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 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.

I've not had a problem viewing this article in IE (8). If you're having a problem with viewing the article, you could go to "print" it and then just copy and paste it into Word to view. Be careful not to repost it on a blog or something like that... copyright laws and all that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

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

You bet. That's the best compliment someone could hope for. I'm humbled. :blush:

I know that we've got some other good people coming up with "SQL Spackle" articles, as well. I'm not sure at what rate they'll be published but a bunch of us got together to pitch in on some of the things that Steve Jones identified as "cracks" in the SQL wall that people need to build.

Thanks again for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

Natalya Bankova (11/15/2010)
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)

Hi Natalya,

The code from the article didn't have any predifined values either. The extra column in your GROUP By is similar to what some of the other folks posted and does cause the code to run a bit slower. Still, it's nice to see people thinking about this. Thank you for taking the time to provide some feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pete Cox SSChasing Mays Points: 636 More actions · Answer 12

Thanks for the pointers Jeff.

I'll SARG and test again

Pete Cox SSChasing Mays Points: 636 More actions · Answer 13

After creating a clustered index on the Months.Monthname (ref my first post) and rearranging the Select as follows, I get about the same performance as Jeff's original select.

SELECT Amount, [Month]

from

(

SELECT

SUM(SomeAmount) AS Amount,

DATENAME(mm,SomeDateTime) AS [Month]

FROM

#MyHead

WHERE

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

GROUP BY

DATENAME(mm,SomeDateTime)

) daa

INNER JOIN

months

ON

[monthname] = [Month]

ORDER BY

months.monthcalendarsequence

That was fun 🙂

Thanks Jeff

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 14

You bet, Pete. Thanks for the feedback. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Edward Boyle-478467 Ten Centuries Points: 1165 More actions · Answer 15

Sorting by Month in SSRS, I use the following

CDate(Parameters!MonthName.Value + " 01, 1900").Month