SQL Ranking Dates to Get Year Over Year Order

Question

I have a list of dates in a YYYYMM format and I am trying to rank them in a Year over Year format that would look like the following:

MonthDisplay  YearMonth  Rank  MonthNumber YearNumber
Aug-2013      201308     1     8           2013
Aug-2012      201208     2     8           2012
Jul-2013      201307     3     7           2013
Jul-2012      201207     4     7           2012

I have been able to get it close by using the following Rank and get the results below:

RANK() OVER(PARTITION BY 1 ORDER BY MonthNumber DESC, YearNumber DESC)

Month     YearMonth  Rank  
Dec-2012  201212     1     
Dec-2011  201112     2     
Nov-2012  201211     114   
Nov-2011  201111     115   
Oct-2012  201210     227   
Oct-2011  201110     228   

However, this starts with Dec-2012 instead of the Aug-2013 (current month). I can't figure out how to get it to start with the current month. I am sure it something super easy and I am just missing it. Thanks!

Answer 1

select
   T.YearMonth,
   rank() over (order by R.rnk asc, D.YearNumber desc) as [Rank],
   D.MonthNumber, D.YearNumber
from Table1 as T
    outer apply (
        select 
            month(getdate()) as CurMonthNumber,
            cast(right(T.YearMonth, 2) as int) as MonthNumber,
            cast(left(T.YearMonth, 4) as int) as YearNumber
    ) as D
    outer apply (
        select
            case
                when D.MonthNumber <= D.CurMonthNumber then
                    D.CurMonthNumber - D.MonthNumber
                else
                    12 + D.CurMonthNumber - D.MonthNumber
            end as rnk      
    ) as R

sql fiddle example