Sort by Calendar month

  • All;

    I am having an issue where my rows are calendar months and my columns are different vendors. I can get the information, but the months are sorted alphabetically not in calendar order. Here is my code:

    I am having the same issue, but I'm using case statements to get the month names.:

    SELECT Category, COUNT(Category) AS CatCount,

    (CASE WHEN Month(OpenDate) = 1 THEN 'January'

    WHEN Month(OpenDate) = 2 THEN 'February'

    WHEN Month(OpenDate) = 3 THEN 'March'

    WHEN Month(OpenDate) = 4 THEN 'April'

    WHEN Month(OpenDate) = 5 THEN 'May'

    WHEN Month(OpenDate) = 6 THEN 'June'

    WHEN Month(OpenDate) = 7 THEN 'July'

    WHEN Month(OpenDate) = 8 THEN 'August'

    WHEN Month(OpenDate) = 9 THEN 'September'

    WHEN Month(OpenDate) = 10 THEN 'October'

    WHEN Month(OpenDate) = 11 THEN 'November'

    WHEN Month(OpenDate) = 12 THEN 'December' END) AS mopendate

    FROM Cloud_Tickets

    WHERE (YEAR(OpenDate) = '2013')

    GROUP BY MONTH(OpenDate), Category

    ORDER BY MONTH(OpenDate)

    Also, if there is a better way to do this, please enlighten me. I am a novice to programming.

    Thank you

    Brian

  • brian.geregach (5/23/2013)


    All;

    I am having an issue where my rows are calendar months and my columns are different vendors. I can get the information, but the months are sorted alphabetically not in calendar order. Here is my code:

    I am having the same issue, but I'm using case statements to get the month names.:

    SELECT Category, COUNT(Category) AS CatCount,

    (CASE WHEN Month(OpenDate) = 1 THEN 'January'

    WHEN Month(OpenDate) = 2 THEN 'February'

    WHEN Month(OpenDate) = 3 THEN 'March'

    WHEN Month(OpenDate) = 4 THEN 'April'

    WHEN Month(OpenDate) = 5 THEN 'May'

    WHEN Month(OpenDate) = 6 THEN 'June'

    WHEN Month(OpenDate) = 7 THEN 'July'

    WHEN Month(OpenDate) = 8 THEN 'August'

    WHEN Month(OpenDate) = 9 THEN 'September'

    WHEN Month(OpenDate) = 10 THEN 'October'

    WHEN Month(OpenDate) = 11 THEN 'November'

    WHEN Month(OpenDate) = 12 THEN 'December' END) AS mopendate

    FROM Cloud_Tickets

    WHERE (YEAR(OpenDate) = '2013')

    GROUP BY MONTH(OpenDate), Category

    ORDER BY MONTH(OpenDate)

    Also, if there is a better way to do this, please enlighten me. I am a novice to programming.

    Thank you

    Brian

    No need to use a case to get the month name. You can use the DATENAME function instead.

    I think this would return the same results.

    select category,

    COUNT(category),

    DATENAME(Month, OpenDate) as mopendate

    from myCTE

    group by Category, DATENAME(Month, OpenDate), MONTH(OpenDate)

    order by month(OpenDate)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • That did give me the same information with a lot less typing. Thank you.

  • You're welcome. Glad that worked for you.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I would make one more change, in the WHERE clause:

    SELECT

    Category,

    COUNT(Category) AS CatCount,

    DATENAME(month, OpenDate) AS mopendate

    FROM

    Cloud_Tickets

    WHERE

    --(YEAR(OpenDate) = '2013')

    OpenDate >= '20130101' AND

    OpenDate < '20140101'

    GROUP BY

    MONTH(OpenDate),

    Category

    ORDER BY

    MONTH(OpenDate);

Viewing 5 posts - 1 through 4 (of 4 total)

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