December 10, 2010 at 10:49 am
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)
will not work because select CONVERT(datetime, 0) equals 1/1/1900, so you need to do MONTH(SomeDateTime) -1 as in:
SELECT [Month] = DATENAME(mm, DATEADD(mm, MONTH(SomeDateTime) -1, 0)), Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime)
Otherwise everything will be off by 1 month
December 12, 2010 at 8:13 pm
Harveysburger (12/10/2010)
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)
will not work because
select CONVERT(datetime, 0)equals 1/1/1900, so you need to do MONTH(SomeDateTime) -1 as in:
SELECT [Month] = DATENAME(mm, DATEADD(mm, MONTH(SomeDateTime) -1, 0)), Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime)
Otherwise everything will be off by 1 month
Whose code are you talking about here?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2010 at 1:26 am
Harveysburger (12/10/2010)
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)
will not work because
select CONVERT(datetime, 0)equals 1/1/1900, so you need to do MONTH(SomeDateTime) -1 as in:
SELECT [Month] = DATENAME(mm, DATEADD(mm, MONTH(SomeDateTime) -1, 0)), Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime)
Otherwise everything will be off by 1 month
This was a problem with the code that I posted back at the beginning of the thread. I agree with this correction, and in fact I posted it myself back on page 3 of the thread after the error was pointed out.
December 13, 2010 at 8:17 am
ah sorry, I didn't notice your own correction when looking over the thread
August 2, 2013 at 1:01 am
Hi Jeff,
As regards why the article got marked down, I'd suggest that it's because you didn't mention the obvious solution.
You want to sort by the month number; In SQL there's a month() function which gives you the month. If you sort by that it works. It's as simple as that. You talk about CTEs etc. but I think you originally overlooked the most obvious solution of all.
While it may be that your solution performs better, that doesn't justify this omission.
I know when you're used to getting 5*s every time, it can be a bit hard to swallow when you're suddenly pulled back to the rank of mere mortal. (but it's probably a good thing now and again ;-))
Thanks,
David McKinney.
August 2, 2013 at 4:17 am
David, you might want to read the article again. The MONTH() function is covered...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 2, 2013 at 4:37 am
Chris,
Are we reading the same article, or have you got a premium subscription? 😉
But how do you display just the month name? Most people end up making a scalar function with a bunch of CASE statements like WHEN MONTH(SomeDate) = 1 THEN 'January' and it's just not necessary.
This is the only reference I find in the article re the month function, and it's got nothing to do with sorting.
Happy to be corrected, but I looked through 25 occurrences of the word month.
August 2, 2013 at 7:29 am
In this example I am sorting by Year And Month. just by including month number in GROUP BY am now able to sort by year and month number. I don't have to select month number.
SELECT [Year] = DATENAME(yyyy,SomeDateTime),
[Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2009-06-01' AND SomeDateTime < '2011'
GROUP BY DATENAME(yyyy,SomeDateTime),DATEPART(m,SomeDateTime),DATENAME(mm,SomeDateTime)
ORDER BY 1,DATEPART(m,SomeDateTime)
;
August 2, 2013 at 7:44 am
David McKinney (8/2/2013)
Hi Jeff,As regards why the article got marked down, I'd suggest that it's because you didn't mention the obvious solution.
You want to sort by the month number; In SQL there's a month() function which gives you the month. If you sort by that it works. It's as simple as that. You talk about CTEs etc. but I think you originally overlooked the most obvious solution of all.
While it may be that your solution performs better, that doesn't justify this omission.
I know when you're used to getting 5*s every time, it can be a bit hard to swallow when you're suddenly pulled back to the rank of mere mortal. (but it's probably a good thing now and again ;-))
Thanks,
David McKinney.
I don't mind someone giving me a low mark. I just mind when someone gives me a low mark and doesn't post a reason why. No one learns a thing that way.
As to the MONTH() thing for sorting instead of the way I did it, try it for the given problem and see why you might want to use the method in the article. Change the ORDER BY in the article to MONTH(SomeDateTime) and see what happens. It's not as simple as you state. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2013 at 8:15 am
How about this way:
SELECT [Month] = datename(mm,min(SomeDateTime)),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY month(SomeDateTime)
ORDER BY month(SomeDateTime)
Apparently the group by expression doesn't have to be present in the SELECT list if you do apply an aggregate on the date. Also it would be preferable to group by the month number and sort by it then group by the month name and then sort by a date derived from that month name.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
August 2, 2013 at 8:19 am
Jeff,
Before my post, I did in fact (quickly) read through all the comments...and the month function was mentioned a number of times already. I didn't see any added value in requoting it....but yes, I do realise that you also have to include it in the 'group by' if that's what you're referring to.
One of your previous comments refers to the sort not happening correctly when using the month function...in fact that's not the case...the sort works fine. In the post you were referencing, it was in fact the month in the select which was incorrect (showing February for January etc..) hence resulting in what appeared to be an incorrect sort.
I should state that my post was an attempt to respond to your very first post, where you ask 'why the low score?' (My post wasn't specifically supposed to be about the month function.)
I should say first that I haven't rated the article today, and don't recall whether I saw / rated the article first time around. But I don't believe I would have scored it poorly - I'd probably have abstained.
I would have abstained, because of the huge respect I have for you on this and other sites. I always love to read what you have to say. For the quality, content, completeness and sheer volume of your articles and forum replies. It's no secret that you're held in uniquely high esteem, by most people who frequent this site. However, it's not because you're often considered a sql god, that I have to worship you, or that I'll be blaspheming if I dare to disagree or challenge. I reserve the right to find some of the things you say, on rare occasions, off the mark - and to say so!
So please take no offence at my 'criticism'...I was attempting just to answer the question you asked.
Hope this helps to clarify.
David.
August 2, 2013 at 8:22 am
Haven't read through all the replies so apologies if this has already been posted!
You could do use this code to group and sort by month AND year:-
;WITH [Data] AS (
SELECT
[SomeMonth] = DATEADD(month, DATEDIFF(month, 0, [SomeDateTime]), 0),
[SomeAmount]
FROM [#MyHead]
)
SELECT
[Month] = RIGHT(CONVERT(VARCHAR(11), [SomeMonth], 106), 8),
[Amount] = SUM([SomeAmount])
FROM [Data]
GROUP BY [SomeMonth]
ORDER BY [SomeMonth]
August 2, 2013 at 8:30 am
David McKinney (8/2/2013)
Jeff,Before my post, I did in fact (quickly) read through all the comments...and the month function was mentioned a number of times already. I didn't see any added value in requoting it....but yes, I do realise that you also have to include it in the 'group by' if that's what you're referring to.
One of your previous comments refers to the sort not happening correctly when using the month function...in fact that's not the case...the sort works fine. ...
David.
I agree, this works perfectly:
SELECT [Month] = datename(mm,min(SomeDateTime)),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY month(SomeDateTime)
ORDER BY month(SomeDateTime)
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
August 2, 2013 at 9:35 am
the sqlist (8/2/2013)
How about this way:
SELECT [Month] = datename(mm,min(SomeDateTime)),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY month(SomeDateTime)
ORDER BY month(SomeDateTime)
Apparently the group by expression doesn't have to be present in the SELECT list if you do apply an aggregate on the date. Also it would be preferable to group by the month number and sort by it then group by the month name and then sort by a date derived from that month name.
My testing shows a consistent performance advantage on my server for your query compared the original query from Jeff's article.
Your query:
CPU time = 31 ms, elapsed time = 26 ms.
Jeff's query:
CPU time = 47 ms, elapsed time = 49 ms.
August 2, 2013 at 10:15 am
I really appreciated this post. I don't think I've ever needed to do this sort of thing, but the solution was a lot cleaner than what I might have come up with on my own. So, thanks for sharing. One thing I've never seen is the CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME) construct. I didn't know that you could use CAST to change a month and year string to DATETIME. That was my key take away from this.
Viewing 15 posts - 61 through 75 (of 97 total)
You must be logged in to reply to this topic. Login to reply