Create Rank and Order for Calendar Month and Year Combination in SQL

user_8661075 0
Viewed 33

I have a date table that has 1 row for every date starting in 1980. This table also includes Calendar Month and Calendar Year columns. I need to create a row number that puts 1 value for each calendar month and year combination in the calendar month/year order.

Here is an example table:

DateID | Date | MonthCode | MonthName | Year
1 | 01/01/1980 | 1 | Jan | 1980
2 | 01/02/1980 | 1 | Jan | 1980
...
828 | 04/07/1982 | 4 | Apr | 1982

I need a value that is unique for every month/year combination

Jan 1980 | 1
Jan 1980 | 1
Feb 1980 | 2
Feb 1980 | 2

This would need to be in calendar order and year order.

I've tried the following, but it doesn't seem to work right:

dense_rank() OVER(partition by calendarMonthName order by  CalendarMonth, calendaryear asc) AS YearMonthRowID

This results in a rank that restarts every month and is in alphabetical order

Any help on getting this formatted correctly?

sql
1 Answers

You can use dense_rank():

select t.*,
       dense_rank() over (order by year, monthcode)
from t
user_11440350
Related