SQL

SQL: Select Week Number of the Month for a Given Year

How to get week number of the month for a given year. It’s not as simple as 52 weeks in a year.

The script below iterates every week from January 1 by simply adding one week.

declare @Year varchar(4) = '2013'

;with aweek(tDate) as
(
    select 
		CAST(@Year + '-1-1' AS DATETIME) tDate
    
    union all
    
    select 
		DATEADD(WEEK, 1, tDate) as tDate
    from aweek
    where YEAR(tDate) <= CAST(@Year AS int)
)

select 
	tDate, 
	DATEPART(WEEK, tDate) weekNo,	
	MONTH(tDate) monthNo, 
	DATEPART(WEEK, tDate) 
		- DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM, 0, tDate), 0)) 
		+ (CASE 
			WHEN (DATEPART(WEEK, monthStart) - DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM, 0, tDate), 0))) = 0 THEN 1
			ELSE 0 END) monthWeekNo
from aweek 
	 left join (select 
					MONTH(tDate) monthNo,
					MIN(tDate) monthStart
				from aweek
				group by MONTH(tDate)
		) AS amonth on MONTH(tDate) = amonth.monthNo
where YEAR(tDate) = @Year

Overlapping Week Number of the Year
The script above is not accurate because it simply adds a week to a date disregarding the first day of a new month.
For example year 2013, 5th week is from January 28 to February 3. There is only one week 5, but two week month. In January it is week 5, in February it is week 1.

Solution to Overlapping Week Number of the Year
The script below will recognize the month transition and reset the date from the 1st. The result is accurate week number of the month, however there will be duplicate week number of the year.

declare @Year varchar(4) = '2013'

;with aweek(tDate) as
(
    select 
		CAST(@Year + '-1-1' AS DATETIME) tDate
	    
    union all
    
    select 
		CASE 
			WHEN MONTH(DATEADD(WEEK, 1, tDate)) > MONTH(tDate) THEN CAST(@Year + '-' + CAST(MONTH(tDate)+1 AS VARCHAR) + '-1' AS DATETIME)
			ELSE DATEADD(WEEK, 1, tDate)
		END as tDate
    from aweek
    where YEAR(tDate) <= CAST(@Year AS int)
)

select
	tDate,
	DATEPART(WEEK, tDate) weekNo,
	MONTH(tDate) monthNo,
	DATEPART(WEEK, tDate) - DATEPART(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, tDate), 0)) + 1 monthWeekNo
from aweek
where YEAR(tDate) = @Year

Cheers – Sy

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s