Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: get max month and year from number

    Hi,

    i have a table that stores month and year in 2 seperate fields as a number, ie month = 12
    year = 2010

    month = 01
    year = 2011

    i need to be able to get the max month and year, but if i just say select max(month) i get 12 instead of 01 (01 is greater in this instance becasue of the year portion. Any way to do this using t-sql?

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM (SELECT number AS m
          FROM master.dbo.spt_values 
          WHERE 'P' = type
             AND number BETWEEN 1 AND 12) AS mm
       CROSS JOIN (SELECT 1900 + number AS y
          FROM master.dbo.spt_values 
          WHERE 'P' = type) AS yy
       ORDER BY CAST(Cast(y AS VARCHAR) + '-' 
    +     CAST(m AS VARCHAR) + '-01' AS DATETIME)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by scabral7 View Post
    i need to be able to get the max month and year
    Code:
    SELECT year
         , MAX(month)
      FROM daTable
     WHERE year = ( SELECT MAX(year) FROM daTable )
    GROUP
        BY year
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    "All roads lead to Rome", and definitely in SQL-land.
    Code:
    with CTE (Year_, Month_, RowNum)
    AS
    	(SELECT Year_, Month_,
    		ROW_NUMBER() OVER (PARTITION BY Year_ ORDER BY Month_ DESC) as RowNum
    	FROM DaTable
    	)
    SELECT Year_, Month_
    FROM CTE
    WHERE CTE.RowNum = 1
    I glued an underscore to Year and Month, to make it clear those are not the reserved words.

    Edit: I should have read the question better. The previous code gives the maximum month for every years. Not the last month of the last year.
    That's what this code does:
    Code:
    with CTE (Year_, Month_, RowNum)
    AS
    	(SELECT Year_, Month_,
    		ROW_NUMBER() OVER (ORDER BY Year_ DESC, Month_ DESC) as RowNum
    	FROM DaTable
    	)
    SELECT CTE.*
    FROM CTE
    WHERE CTE.RowNum = 1
    Last edited by Wim; 03-03-11 at 08:50.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I figured I'd add one too. More along the lines of Keep It Simple, Stupid(KISS)

    Code:
    select max(digits(year_) || digits(month_))
      from my_table
    Dave Nance

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    ... More along the lines of Keep It Simple, Stupid(KISS)
    The art is to make it as simple as it can get, not simpler.

    This query will give the maximum year and the maximum month, not the maximum month of the maximum year.

    On the data
    Year Month
    2009 12
    2010 01

    It will not return 2010 01, but 2010 12.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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