Sorting Months By Number (SQL Spackle)

  • Jeff Moden

    SSC Guru

    Points: 993753

    Comments posted to this topic are about the item Sorting Months By Number (SQL Spackle)

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993753

    Heh... Yowch! I guess I should only expect 3 stars because these types of articles are so very short and written to specific problems that several folks said they'd like to see. Still, it would be nice to hear back from those folks that gave the lower ratings so we can find out what they'd really like to see. 😉

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • N.North

    SSC Journeyman

    Points: 91

    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)

  • Pete Cox

    SSChasing Mays

    Points: 636

    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

  • Ola L Martins-329921

    Mr or Mrs. 500

    Points: 512

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

  • Henk Schreij

    SSCarpal Tunnel

    Points: 4553

    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)

  • hugo-939487

    SSC Enthusiast

    Points: 137

    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)

    ;

  • james.wheeler10

    SSC Enthusiast

    Points: 127

    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?

  • manub22

    Hall of Fame

    Points: 3133

    How about this:

    select datename(M,convert(varchar,months)+'/01/2010') as days

    from (select 1 as months union

    select 2 as months union

    select 3 as months union

    select 4 as months union

    select 5 as months union

    select 6 as months union

    select 7 as months union

    select 8 as months union

    select 9 as months union

    select 10 as months union

    select 11 as months union

    select 12 as months ) as M

    order by convert(datetime,convert(varchar,months)+'/01/2010')

  • sharath.chalamgari

    SSCertifiable

    Points: 5680

    SELECT [Month] = DATENAME(mm,SomeDateTime),Year(Somedatetime),

    Amount = SUM(SomeAmount)

    FROM #MyHead

    GROUP BY DATENAME(mm,SomeDateTime),Year(Somedatetime)

    ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME),Year(Somedatetime)

    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?

  • Kristian Ask

    SSC Eights!

    Points: 857

    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)

    ;

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

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    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.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Grant Fritchey

    SSC Guru

    Points: 395264

    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.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Geoff A

    SSChampion

    Points: 11407

    Kristian Ask (11/15/2010)


    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)

    ;

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

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

  • N.North

    SSC Journeyman

    Points: 91

    Geoff A (11/15/2010)


    Kristian Ask (11/15/2010)


    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)

    ;

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

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

    That's really interesting - I assumed ordering by MONTH(SomeDateTime) would have to be more efficient. I still prefer it aesthetically, but it's a useful reminder not to make assumptions.

Viewing 15 posts - 1 through 15 (of 98 total)

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