Given a set of records with dates from say 10/1/2009 to 3/31/2010, I want to assign each record an ordinal month number. So 10/1/2009 through 10/31/2009 would get a value of 1, 1/1/2010 through 1/31/2010 would get a value of 4, etc. Following this pattern, a date of 1/1/2011 would get a value of 16.
The solution needs to be generic enough to work with any date range.
I think this is a job for the RANK() function but I can't figure it out.