November 15, 2010 at 7:04 am
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.....
November 15, 2010 at 7:08 am
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!
November 15, 2010 at 7:10 am
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.
November 15, 2010 at 7:11 am
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]
November 15, 2010 at 7:18 am
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.
November 15, 2010 at 7:19 am
All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.
November 15, 2010 at 7:31 am
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.
November 15, 2010 at 7:43 am
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
November 15, 2010 at 8:27 am
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.
November 15, 2010 at 8:29 am
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.
November 15, 2010 at 8:31 am
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
November 15, 2010 at 8:32 am
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)
November 15, 2010 at 8:33 am
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.
November 15, 2010 at 8:50 am
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.
November 15, 2010 at 8:55 am
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)
Viewing 15 posts - 16 through 30 (of 97 total)
You must be logged in to reply to this topic. Login to reply