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?
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
where rn = 1;