Solved

# TSQL - Rank months within date range

Posted on 2010-04-08
Medium Priority
594 Views
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.
0
Question by:wlevy
• 2

LVL 143

Expert Comment

ID: 30144672
datediff(month, '10/1/2009', yourfield)
0

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
``````
0

Author Comment

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

LVL 10

Expert Comment

ID: 30151560
Note, if you are missing entries for any one month it will not skip a rank number
so

01 jan 2010, 1
01 mar 2010, 2
0

## Featured Post

Question has a verified solution.

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