Sorting Months By Number (SQL Spackle)

  • 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

  • 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

  • Yes, agree

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

     

     

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • @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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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