Sorting Months By Number (SQL Spackle)

Question

Sorting Months By Number (SQL Spackle)

Viewing 8 posts - 91 through 97 (of 97 total)

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

N.North SSC Journeyman Points: 91 More actions · Answer 1

Thanks for following up and testing the different solutions Jeff - makes for interesting reading. The personal lesson for me from this is "test your code before posting". That way I could now be "N. North" rather than "N.North (corrected)".

Nick

David McKinney SSChampion Points: 10472 More actions · Answer 2

Yep. For single rows, that will work just fine. Try your code with a GROUP BY and a SUM on the Sale column and see what happens. 😉

...below is my latest in the 'compatible' format - perhaps you can add to your collection, Jeff. Thanks....David.

--===== Query by dmckinney ==========================================================

-- (http://www.sqlservercentral.com/Forums/FindPost1480386.aspx)

SELECT

[Month] = DATENAME(m,DATEADD(m,DATEDIFF(m,0,SomeDateTime)%12,0)),

Amount = SUM(SomeAmount)

FROM #MyHead

WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011' --changed dates to same as elsewhere

GROUP BY DATEDIFF(m,0,SomeDateTime)%12

ORDER BY DATEDIFF(m,0,SomeDateTime)%12

mohit.sharma 99294 SSC Rookie Points: 32 More actions · Answer 3

mohit.sharma 99294

SSC Rookie

Points: 32

August 13, 2013 at 10:22 pm

#1641209

Yes, agree

Peter Heller Ten Centuries Points: 1000 More actions · Answer 4

How about adding the month(SomeDateTime) for the sorting correctly without affecting the group by?

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);

Nick Ryan SSCrazy Points: 2237 More actions · Answer 5

As this article has been resurrected from the distant past, how about amending the article to show the rather more efficient and slightly more obvious looking use of the MONTH() function instead?

As already shown in the much earlier comments by earlier commenters,

SELECT
DATENAME(mm,DATEFROMPARTS(1900,MONTH(SomeDateTime),1)),
Amount  = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime);

DATEFROMPARTS() is rather newer than the original article, however a similar trick was possible just less clear. Like anything, there are doubtless many ways of doing this and I expect there's a cleaner method that someone will demonstrate shortly...

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

Thanks for the feedback folks.

To be honest, I should rewrite the whole, very old article. Using MONTH (which returns on 1-12 regardless of year) or anything else that doesn't observe the year of the data is just asking for trouble especially because the computational errors produced by a span of years will be totally silent.

--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)

s.bellini Grasshopper Points: 16 More actions · Answer 7

In my experience people abuse tempdb, please consider:

select [Month] = DATENAME(mm,SomeDateTime),

--[mm] = datepart(mm, SomeDateTime),

Amount = SUM(SomeAmount)

from

(

select TOP (10000000)

SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),

SomeAmount = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY)

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

) t

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

group by DATENAME(mm,SomeDateTime), datepart(mm, SomeDateTime)

order by datepart(MONTH, SomeDateTime)

same result but faster and - for me - simplier

bye

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

@S.bellini

Yes, I appreciate that a lot of people abuse TempDB and thanks for the feedback there. For these type of articles, though, I can't presume that it's safe to sometimes drop or even create a table and so I use Temp Tables for this type of testing.

--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)