Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Finding the last day of the year

Respondida Finding the last day of the year

  • Tuesday, April 17, 2012 2:44 PM
     
     
    Sign In to Vote
    0

    I have a series of dates that constitute 5 years of financial data. I need to find the last day recorded for a year. Problem is the data is in trading days and doesn't always end on the 31st of december. Dates are in yyyy-mm-dd format using the date datatype. I can find the last day of an individual year using the MAX function no problem. The question is how can I find the last day for every year in my dataset WITHOUT using cursors?

     

All Replies

  • Tuesday, April 17, 2012 2:47 PM
     
     Answered
    Sign In to Vote
    0

    SELECT DATEPART ( yyyy , datefield ) as DateYear, MAX(DateField)

    FROM TABLE

    GROUP BY DATEPART ( yyyy , datefield )

    Chuck

    • Marked As Answer by falcon00 Wednesday, April 18, 2012 4:59 AM
    •  
     
  • Tuesday, April 17, 2012 2:47 PM
     
     Answered Has Code
    Sign In to Vote
    0
     
    select max(date)
from table
group by year(date)
    • Marked As Answer by falcon00 Wednesday, April 18, 2012 4:59 AM
    •  
     
  • Tuesday, April 17, 2012 2:50 PM
     
     
    Sign In to Vote
    0

    Thanks guys. It will be a bit before I can check these but obviously I was thinking WAY too hard. :S

     
  • Tuesday, April 17, 2012 3:00 PM
    Moderator
     
     
    Sign In to Vote
    0

    You can also find the last trading by year, either using a ranking function, the aPPLY operator, or a join between the table and a derived table that selects the MAX trading date by year. The latest one is a little bit more complicated than it looks.

    with R as (
    select *, row_number() over(partition by year(trading_dt) order by trading_dt DESC, trading_id) as rn
    from T
    )
    select *
    from R
    where rn = 1;

    AMB

    Some guidelines for posting questions...

     
  • Tuesday, April 17, 2012 4:56 PM
     
     
    Sign In to Vote
    0

    Please check this link. Already it has been marked as answer.

     
  • Tuesday, April 17, 2012 5:14 PM
     
     
    Sign In to Vote
    0

    Already read those hence this post.