Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: [RESOLVED] Sort months chronologically not alphabetically

    I'm running this query and it's returning the correct results but sorting Feb-Jan-Mar and I want it to sort Jan-Feb-Mar:

    select
    DATENAME(month, [Date Id Number Added]) + ' ' + DATENAME(year, [Date Id number added]) as theMonth,
    Count([ID Number]) AS [No of ID's],
    max ([ID Number]) as [Last Id Number]
    FROM [BE_Table]
    GROUP BY DATENAME(year, [Date Id Number Added]), DATENAME(month, [Date Id Number Added])
    HAVING Count ([BE_Table].[ID Number]) Is Not Null
    AND DATENAME(year,[Date ID Number Added])='2006'
    ORDER BY DATENAME(month, [Date Id Number Added])

    February 2006 38 25063
    January 2006 37 25025
    March 2006 19 25082

    Because of the complex relationship (IMO) between aggregates and HAVING and GROUP BY, I am having trouble changing this so that it still works plus is an improvement over what it's currently doing.

    Thanks.
    Last edited by M Mock; 08-07-07 at 10:20. Reason: resolved

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT 
       DateName(month, [Date Id Number Added]) + ' ' 
    +  DateName(year, [Date Id number added]) as theMonth
    ,  Count([ID Number]) AS [No of ID's]
    ,  max ([ID Number]) as [Last Id Number]
       FROM [BE_Table]
       GROUP BY DateName(year, [Date Id Number Added])
    ,     DateName(month, [Date Id Number Added])
       HAVING Count ([BE_Table].[ID Number]) Is Not Null
          AND DateName(year,[Date ID Number Added])='2006'
       ORDER BY [Date Id Number Added]
    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    If the output is going to an application, let the app handle the presentation.

    If you are using sql:
    1. Put the results of your in a #temp table
    2. Create another #temp table ((MonthNo int, Monthname varchar(20)) with the static values (1, "January'), (2, 'February') etc,
    3. Then do a final join between #temp tables and order the output by MonthNo.

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jul 2006
    Posts
    111
    Thank you for your speedy replies.

    PatP - I got this error when I ran your query:
    Column "BE_Table.Date ID Number Added" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

    That's what I meant by the complex relationship...

    tomh53 - I'm writing this query to support a Crystal Report. I am floundering because I have no experience with reports. I am converting an Access Application to vb.net 2005. So I am taking the query that worked in Access and trying to port it. Your idea might work, I'm just not sure yet what I'm doing! Once I get my query to return the correct results, I have to learn how to hook it into Crystal.

    Thanks. I'm sure there will be more to come!
    Marlene

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when converting access sql to sql server sql, always examine the HAVING clauses and eliminate if possible or convert to WHERE clause if possible
    Code:
    select DATENAME(month, [Date Id Number Added]) + ' 2006' as theMonth
         , Count([ID Number]) AS [No of ID's]
         , max([ID Number]) as [Last Id Number]
      FROM [BE_Table]
    GROUP 
        BY DATENAME(month, [Date Id Number Added])
     WHERE [Date ID Number Added] >= '2006-01-01'
       AND [Date ID Number Added]  < '2007-01-01'
    ORDER 
        BY CHARINDEX(LEFT(DATENAME(month, [Date Id Number Added]),3)
             , 'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I like rudy's solution for output to Crystal. Mine is a quick and dirty from a DBA standpoint. His is elegant from a developer's view.

    Nice job rudy!

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Jul 2006
    Posts
    111
    Thank you all. r937, I just had to flip the WHERE and the GROUP BY:

    select DATENAME(month, [Date Id Number Added]) + ' 2006' as theMonth
    , Count([ID Number]) AS [No of ID's]
    , max([ID Number]) as [Last Id Number]
    FROM [BE_Table]
    WHERE [Date ID Number Added] >= '2006-01-01'
    AND [Date ID Number Added] < '2007-01-01'
    GROUP
    BY DATENAME(month, [Date Id Number Added])
    ORDER
    BY CHARINDEX(LEFT(DATENAME(month, [Date Id Number Added]),3)
    , 'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')

    So thanks for this solution. I like it when I actually learn something new.

    Also, I had no idea about HAVING so thanks for the tip.

    Marlene

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by M Mock
    Thank you all. r937, I just had to flip the WHERE and the GROUP BY:
    DOH!! :blush:

    sometimes i type too fast and forget to proofread

    glad you got it sorted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •