November 15, 2010 at 9:07 am
You can also add a grouping on MONTH(SomeDateTime) without adding it to the SELECT list. It won't actually change the grouping at all, since grouping on month name and month number will result in the same grouping. Then you can just order by that instead of making a cast:
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)
November 15, 2010 at 9:44 am
Hopelessly inefficient but I've always had a soft spot for this as a quirky solution to the problem:
SELECT [Month] = DATENAME(mm,SomeDateTime)
, Amount = SUM(SomeAmount)
FROM dbo.month_order_test
WHERE SomeDateTime >= '2010'
AND SomeDateTime < '2011'
GROUP BY
DATENAME(mm,SomeDateTime)
ORDER BY
CHARINDEX(DATENAME(mm,SomeDateTime), 'JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember');Tested in 2008
November 15, 2010 at 12:07 pm
How much more efficient is the proposed solution ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)than adding a numerical representation of the month, whether by using MONTH() or DATEPART(), to the GROUP BY and then ordering on that? GROUP BY DATENAME(mm,SomeDateTime)
,datepart(mm,somedatetime)
order by datepart(mm,somedatetime) It seems to me that the readablity of the second approach would far outweigh any efficiency advantage in sorting a mere 12 result rows. What am I missing here?
November 15, 2010 at 12:53 pm
john.arnott (11/15/2010)
How much more efficient is the proposed solutionORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)than adding a numerical representation of the month, whether by using MONTH() or DATEPART(), to the GROUP BY and then ordering on that?GROUP BY DATENAME(mm,SomeDateTime)It seems to me that the readablity of the second approach would far outweigh any efficiency advantage in sorting a mere 12 result rows. What am I missing here?,datepart(mm,somedatetime)
order by datepart(mm,somedatetime)
from your local PC running an instance for testing, the answer is almost none.
from an application front end making the call 10,000 times an hour...... it would be HUGE!
its always the same with optimization. most people will look at the stored procedure that takes 30 seconds to run (even though it is run only 3 times a day) and ignore the stored procedure that is run 500,000 times a days and takes 300 MS....
November 15, 2010 at 1:37 pm
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.
November 15, 2010 at 1:50 pm
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!
james.wheeler10 - Grasshopper (11/15/2010)
What happens when you're reporting on a period greater than a year?
Add a DATEPART(yyyy, SomeDateTime) to the select-, group by- and order by- clauses
November 15, 2010 at 2:29 pm
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:
November 15, 2010 at 10:30 pm
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)
That edges out the code I posted except, as others pointed out, it doesn't quite get the sort right. I do appreciate the time you spent for building the feedback, though.
--Jeff Moden
November 15, 2010 at 10:48 pm
Pete Cox (11/15/2010)
Hi Jeff,I might consider using something like this approach
CREATE TABLE [dbo].[Months](
[MonthName] [nvarchar](10) NOT NULL,
[MonthCalendarSequence] [int] NOT NULL,
[MonthFinancialSequance] [int] NOT NULL
) ON [PRIMARY]
Insert into Months Values ('January',1,10)
Insert into Months Values ('February',2,11)
Insert into Months Values ('March',3,12)
Insert into Months Values ('April',4,1)
Insert into Months Values ('May',5,2)
Insert into Months Values ('June',6,3)
Insert into Months Values ('July',7,4)
Insert into Months Values ('August',8,5)
Insert into Months Values ('September',9,6)
Insert into Months Values ('October',10,7)
Insert into Months Values ('November',11,8)
Insert into Months Values ('December',12,9)
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead h inner join months m on m.monthname = DATENAME(mm,SomeDateTime)
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATENAME(mm,SomeDateTime)
order by max(m.monthcalendarsequence)
Can't really decide what would be best
Pete
Thanks for the feedback, Pete. Since I provided all the test data needed in the article, the way to decide is to simply test it with SQL Profiler or SET STATICS turned on.
As your code is, it's about twice as slow as the code from the article with or without indexes simply because you have some non-SARGable predicates in the JOIN, WHERE, and ORDER BY clauses.
I do, however, fully endorse the idea of using a calendar table (month table in this case) especially when the fiscal calendar is different. You just need to wiggle SARGability into your code so you get an index seek on the MONTHS calendar table.
--Jeff Moden
November 15, 2010 at 10:52 pm
Ola L Martins-329921 (11/15/2010)
Maybe the low ratings is due to the fact that most programmers and db-developers "know" this solution already: The old "sort numbers stored as text as numbers"... ("1" ,"2"..."10", "11" and NOT "1", "10", "11", "2", "3"...).I like the technique, but you should clarify "any year" is not actually any year, it is a valid year within the sql server time span...
True on both parts. Yeah... Steve asked us to write some short articles that he's seen a lot of in the SSC forums. I hope everyone understands that these are meant to be short and they'll seem awfully obvious to experienced users. These are mostly for inexperienced users that need some quick lessons on common tasks.
Thanks for the feedback.
--Jeff Moden
November 15, 2010 at 11:07 pm
Henk Schreij (11/15/2010)
Jeff Moden (11/15/2010)
Still, it would be nice to hear back from those folks that gave the lower ratings . . .I didn't give a low rating, but found a slip of the pen (it confused me for a moment).
-- This builds a table with random dates and amounts for 20 years-- starting in the year 2000.
SELECT TOP (1000000)
SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),
The 2000 from the comment is not the same as the CAST('2005' AS DATETIME)
I'll see if I can get Steve to change that. I'm not sure how I missed that copy/paste mistake. Thanks for pointing it out.
--Jeff Moden
November 15, 2010 at 11:14 pm
james.wheeler10 (11/15/2010)
What happens when you're reporting on a period greater than a year?Won't both of the overlapping month's figures will be aggregated into a single month aggregate total?
Absolutely correct. Yes it would aggregate similar months from different years under the same month. I guess I need to make the problem definition more clear (a single year was intended) and, perhaps, show a solution that would handle multiple years. Of course, the report really wouldn't make any sense unless you also included year. Thank you for the feedback.
{edit} I see that sharath.chalamgari already beat me to the task. Thanks, Sharath.
--Jeff Moden
November 15, 2010 at 11:21 pm
Kristina-868114 (11/15/2010)
You can also add a grouping on MONTH(SomeDateTime) without adding it to the SELECT list. It won't actually change the grouping at all, since grouping on month name and month number will result in the same grouping. Then you can just order by that instead of making a cast: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)
Good thinking. Seems that you and Hugo had the same idea. Thanks for the feedback, Kristina.
--Jeff Moden
November 15, 2010 at 11:26 pm
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)
;
Spot on. Glad to see folks trying different things out. The extra calculation in the GROUP BY does slow it down a bit, though.
--Jeff Moden
November 15, 2010 at 11:28 pm
Trey Staker (11/15/2010)
Ola L Martins-329921 (11/15/2010)
Maybe the low ratings is due to the fact that most programmers and db-developers "know" this solution already: The old "sort numbers stored as text as numbers"... ("1" ,"2"..."10", "11" and NOT "1", "10", "11", "2", "3"...).I like the technique, but you should clarify "any year" is not actually any year, it is a valid year within the sql server time span...
I agree with you that most programmers and db-developers should already know this. Also as Jeff wrote in his article this is something that when possible should be handled by the application. However this article was requested because it keeps coming up as a question in the forums. Also remember that a lot of the people who visit these forums may not be as advanced as you.
Jeff, great "Spackle" article. Thanks.
Thanks, Trey. As is usual, the real learning occurs in the discussions. Ola didn't mean anything bad by this. Ola was just providing a possible answer to my question as to the ratings this thread started out with and I believe Ola probably hit the nail on the head.
--Jeff Moden
Viewing 15 posts - 31 through 45 (of 97 total)
You must be logged in to reply to this topic. Login to reply