Creating A Sortable Key Using ROW_NUMBER
I recently had a case where I wanted to get a regular academic term n terms from a given date. For example if I was in the Fall Term of 2018, I wanted to be able to get the term 2 terms before which would be Spring 2018. The problem is we use term codes, which have changed formatting over the years, and we also have partial terms which I didn't want to consider. The data ends up looking something like this: TermSK TermCode 1 201780 6 201810 17 201850 SELECT ROW_NUMBER() over(order by term_code desc) Row_Num, Term_Code FROM dimTerm WHERE Term_Type = 'Regular' Row_Num TermCode 3 201780 2 201810 1 201850 The results made it really easy to grab any term preceding by simply subtracting the desired previous terms from the generated Row_Num.