TSQL - Rank months within date range

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
datediff(month, '10/1/2009', yourfield)
Try this. Uses DENSE_RANK so all of the same month get the same rank, also converts each date to the 1st of the month to get the rank by month. (test table just for demo purposes)
DECLARE	@test TABLE(id int, MyDate DATETIME)
INSERT	@test
SELECT 1,'01 oct 2009' UNION ALL
SELECT 2,'05 oct 2009' UNION ALL
SELECT 3,'07 nov 2009' UNION ALL
SELECT 4,'13 dec 2009' UNION ALL
SELECT 5,'17 dec 2009' UNION ALL
SELECT 6,'01 jan 2010' UNION ALL
SELECT 7,'13 feb 2010'

SELECT	id, MyDate, DENSE_RANK() OVER (ORDER BY DATEADD(mm, DATEDIFF(mm,0,MyDate), 0)/*get 1st day of that month*/ ASC) AS rank
FROM	@test

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wlevyAuthor Commented:
Bodestone, that's exactly what I was looking for. Thank you!
Note, if you are missing entries for any one month it will not skip a rank number

01 jan 2010, 1
01 mar 2010, 2
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.