InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 10:51:36 GMT20http://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxah sorry, I didn't notice your own correction when looking over the threadMon, 13 Dec 2010 08:17:39 GMTHarveysburgerhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Harveysburger (12/10/2010)[/b][hr][code] 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)[/code]will not work because [code]select CONVERT(datetime, 0)[/code] equals 1/1/1900, so you need to do MONTH(SomeDateTime) -1 as in:[code] 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)[/code]Otherwise everything will be off by 1 month[/quote]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.Mon, 13 Dec 2010 01:26:30 GMTN.Northhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Harveysburger (12/10/2010)[/b][hr][code] 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)[/code]will not work because [code]select CONVERT(datetime, 0)[/code] equals 1/1/1900, so you need to do MONTH(SomeDateTime) -1 as in:[code] 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)[/code]Otherwise everything will be off by 1 month[/quote]Whose code are you talking about here?Sun, 12 Dec 2010 20:13:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[code] 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)[/code]will not work because [code]select CONVERT(datetime, 0)[/code] equals 1/1/1900, so you need to do MONTH(SomeDateTime) -1 as in:[code] 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)[/code]Otherwise everything will be off by 1 monthFri, 10 Dec 2010 10:49:23 GMTHarveysburgerhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxSorting by Month in SSRS, I use the following CDate(Parameters!MonthName.Value + " 01, 1900").MonthMon, 22 Nov 2010 05:41:04 GMTEdward Boyle-478467http://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxYou bet, Pete. Thanks for the feedback. :-)Wed, 17 Nov 2010 17:46:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxAfter 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. [code="sql"]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)) daaINNER JOINmonthsON [monthname] = [Month]ORDER BY months.monthcalendarsequence[/code]That was fun :-)Thanks JeffWed, 17 Nov 2010 03:30:54 GMTPete Coxhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxThanks for the pointers Jeff.I'll SARG and test againWed, 17 Nov 2010 01:35:09 GMTPete Coxhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Natalya Bankova (11/15/2010)[/b][hr]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)[/quote]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.Tue, 16 Nov 2010 00:28:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]sdorris-1150234 (11/15/2010)[/b][hr]All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.[/quote]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.Tue, 16 Nov 2010 00:19:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]ejoell 66477 (11/15/2010)[/b][hr]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.[/quote]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.Tue, 16 Nov 2010 00:13:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Robert Dudley (11/15/2010)[/b][hr]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 ) aGROUP BY [Month], [SortMonth]ORDER BY [SortMonth][/quote]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.Tue, 16 Nov 2010 00:08:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Ola L Martins-329921 (11/15/2010)[/b][hr][quote][b]Steve Jones - SSC Editor (11/15/2010)[/b][hr]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.[/quote]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![/quote]Just to be clear, I didn't take it as a slight at all. The clarification probably has Steve breathing a bit easier, though. :-P I also very much appreciate the "outside-the-box" comment. Thanks, Ola.Tue, 16 Nov 2010 00:01:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Michael Valentine Jones (11/15/2010)[/b][hr]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.[/quote]Heh.... not to worry, Michael. If I got a bad rating from you, I'd probably deserve it and I know you'd explain.Mon, 15 Nov 2010 23:57:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]manub22 (11/15/2010)[/b][hr]How about this:[code="sql"]select datename(M,convert(varchar,months)+'/01/2010') as daysfrom (select 1 as months unionselect 2 as months unionselect 3 as months unionselect 4 as months unionselect 5 as months unionselect 6 as months unionselect 7 as months unionselect 8 as months unionselect 9 as months unionselect 10 as months unionselect 11 as months unionselect 12 as months ) as Morder by convert(datetime,convert(varchar,months)+'/01/2010')[/code][/quote]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.Mon, 15 Nov 2010 23:55:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Dugi (11/15/2010)[/b][hr][quote][b]sdorris-1150234 (11/15/2010)[/b][hr]All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.[/quote]I vote for this ... Jeff, thanks for the nice article! :hehe:[/quote]Ah, thank you ol' friend. Thanks for stopping by.Mon, 15 Nov 2010 23:51:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Dean Cochrane (11/15/2010)[/b][hr]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.[/quote]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.Mon, 15 Nov 2010 23:44:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Geoff A (11/15/2010)[/b][hr][quote][b]Kristian Ask (11/15/2010)[/b][hr][quote][b]hugo-939487 (11/15/2010)[/b][hr]Another variaton, using the MONTH function:[code="other"] 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);[/code][/quote]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).[/quote]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.....[/quote]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. :-)Mon, 15 Nov 2010 23:39:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Grant Fritchey (11/15/2010)[/b][hr]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.[/quote]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.Mon, 15 Nov 2010 23:30:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Trey Staker (11/15/2010)[/b][hr][quote][b]Ola L Martins-329921 (11/15/2010)[/b][hr]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...[/quote]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.[/quote]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.Mon, 15 Nov 2010 23:28:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]hugo-939487 (11/15/2010)[/b][hr]Another variaton, using the MONTH function:[code="other"] 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);[/code][/quote]Spot on. Glad to see folks trying different things out. The extra calculation in the GROUP BY does slow it down a bit, though.Mon, 15 Nov 2010 23:26:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Kristina-868114 (11/15/2010)[/b][hr]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)[/quote]Good thinking. Seems that you and Hugo had the same idea. Thanks for the feedback, Kristina.Mon, 15 Nov 2010 23:21:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]james.wheeler10 (11/15/2010)[/b][hr]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?[/quote]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.Mon, 15 Nov 2010 23:14:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Henk Schreij (11/15/2010)[/b][hr][quote][b]Jeff Moden (11/15/2010)[/b][hr] Still, it would be nice to hear back from those folks that gave the lower ratings . . .[/quote]I didn't give a low rating, but found a slip of the pen (it confused me for a moment).[code="sql"]-- 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),[/code]The 2000 from the comment is not the same as the CAST('2005' AS DATETIME)[/quote]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.Mon, 15 Nov 2010 23:07:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Ola L Martins-329921 (11/15/2010)[/b][hr]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...[/quote]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.Mon, 15 Nov 2010 22:52:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Pete Cox (11/15/2010)[/b][hr]Hi Jeff,I might consider using something like this approachCREATE 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 bestPete[/quote]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.Mon, 15 Nov 2010 22:48:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]N.North (11/15/2010)[/b][hr]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:[code="sql"] 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)[/code][/quote]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.Mon, 15 Nov 2010 22:30:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]sdorris-1150234 (11/15/2010)[/b][hr]All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.[/quote]I vote for this ... Jeff, thanks for the nice article! :hehe:Mon, 15 Nov 2010 14:29:23 GMTDugihttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Steve Jones - SSC Editor (11/15/2010)[/b][hr]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.[/quote]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![quote][b]james.wheeler10 - Grasshopper (11/15/2010)[/b][hr]What happens when you're reporting on a period greater than a year?[/quote]Add a DATEPART(yyyy, SomeDateTime) to the select-, group by- and order by- clausesMon, 15 Nov 2010 13:50:16 GMTOla L Martins-329921http://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxThere 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.Mon, 15 Nov 2010 13:37:54 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]john.arnott (11/15/2010)[/b][hr]How much more efficient is the proposed solution [code="sql"]ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)[/code]than adding a numerical representation of the month, whether by using MONTH() or DATEPART(), to the GROUP BY and then ordering on that? [code="sql"]GROUP BY DATENAME(mm,SomeDateTime) ,datepart(mm,somedatetime) order by datepart(mm,somedatetime) [/code]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?[/quote]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....Mon, 15 Nov 2010 12:53:51 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxHow much more efficient is the proposed solution [code="sql"]ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)[/code]than adding a numerical representation of the month, whether by using MONTH() or DATEPART(), to the GROUP BY and then ordering on that? [code="sql"]GROUP BY DATENAME(mm,SomeDateTime) ,datepart(mm,somedatetime) order by datepart(mm,somedatetime) [/code]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?Mon, 15 Nov 2010 12:07:39 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxHopelessly inefficient but I've always had a soft spot for this as a quirky solution to the problem:[code="sql"]SELECT [Month] = DATENAME(mm,SomeDateTime) , Amount = SUM(SomeAmount)FROM dbo.month_order_testWHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'GROUP BY DATENAME(mm,SomeDateTime)ORDER BY CHARINDEX(DATENAME(mm,SomeDateTime), 'JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember');[/code]Tested in 2008Mon, 15 Nov 2010 09:44:51 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxYou 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)Mon, 15 Nov 2010 09:07:42 GMTkristinamhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxI 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 2Column 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.[code="sql"]select [Month] = datename(mm,SomeDateTime), Amount = sum(SomeAmount)from #MyHeadwhere 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)[/code]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.[code="sql"]select [Month] = datename(month,max(dateadd(mm,datediff(mm,0,SomeDateTime),0))) , Amount = sum(SomeAmount)from #MyHeadwhere SomeDateTime >= '2010' AND SomeDateTime < '2011'group by dateadd(mm,datediff(mm,0,SomeDateTime),0)order by dateadd(mm,datediff(mm,0,SomeDateTime),0)[/code]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).[code="sql"]select [Month] = datename(month,dateadd(mm,datediff(mm,0,SomeDateTime),0)) , Amount = sum(SomeAmount)from #MyHeadwhere SomeDateTime >= '2010' AND SomeDateTime < '2011'group by dateadd(mm,datediff(mm,0,SomeDateTime),0)order by dateadd(mm,datediff(mm,0,SomeDateTime),0)[/code]Mon, 15 Nov 2010 08:55:12 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxThe 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.Mon, 15 Nov 2010 08:50:29 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]Steve Jones - SSC Editor (11/15/2010)[/b][hr][quote][b]ejoell 66477 (11/15/2010)[/b][hr]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.[/quote]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.[/quote]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. :-P Not sure why my horizontal scrolling isn't working though.Mon, 15 Nov 2010 08:33:52 GMTthisisfutilehttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxSame 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)Mon, 15 Nov 2010 08:32:43 GMTNatalya Bankovahttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspx[quote][b]ejoell 66477 (11/15/2010)[/b][hr]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.[/quote]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.HTHMon, 15 Nov 2010 08:31:05 GMTthisisfutilehttp://www.sqlservercentral.com/Forums/Topic1020369-203-1.aspxI'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.Mon, 15 Nov 2010 08:29:50 GMTSteve Jones - SSC Editor