TSQL - Rank months within date range

Posted on 2010-04-08
Medium Priority
Last Modified: 2012-05-09
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.
Question by:wlevy
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 30144672
what about this:
datediff(month, '10/1/2009', yourfield)
LVL 10

Accepted Solution

Bodestone earned 2000 total points
ID: 30146582
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


Author Comment

ID: 30151076
Bodestone, that's exactly what I was looking for. Thank you!
LVL 10

Expert Comment

ID: 30151560
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question