Calendar Table With ISO_Week



 
DECLARE  @Year SMALLINT = 2015, @FirstISOWKDay DATETIME

SELECT @FirstISOWKDay=dateadd(week, Datediff(week,0,datefromparts(@year,1,1)),-1)

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n)-1 FROM Num2)
----0-10200

SELECT  
          theDay    = DATEADD(DAY, n*7+num, @FirstISOWKDay)
		 ,Start    = DATEADD(DAY, n*7, @FirstISOWKDay)
        ,[End]      = DATEADD(DAY, n*7 + 6, @FirstISOWKDay)
        ,Friendly   = CONCAT(format(DATEADD(DAY, n*7, @FirstISOWKDay),'MMM dd, yyyy'),' To ', format(DATEADD(DAY, n*7+6, @FirstISOWKDay),'MMM dd, yyyy'))
        ,ISOWEEK    = DATEPART(ISO_WEEK, DATEADD(DAY, n*7, @FirstISOWKDay+1))
		,ISO8601Year = Case 
		When 
		Year(DATEADD(DAY, n*7, @FirstISOWKDay))<>Year(DATEADD(DAY, n*7 + 6, @FirstISOWKDay)) Then		
		Case when  DATEPART(ISO_WEEK, DATEADD(DAY, n*7, @FirstISOWKDay+1))=1 Then Year(DATEADD(DAY, n*7 + 6, @FirstISOWKDay))  
		when  DATEPART(ISO_WEEK, DATEADD(DAY, n*7, @FirstISOWKDay+1))>1 Then Year(DATEADD(DAY, n*7 , @FirstISOWKDay))
		End 
		Else
		Year(DATEADD(DAY, n*7+num, @FirstISOWKDay))
		End  
		 
FROM Nums
 
cross apply (values(0),(1),(2),(3),(4),(5),(6)) d(num)

WHERE DATEPART(YEAR, DATEADD(DAY, n*7 + 6, @FirstISOWKDay)) >= @Year and DATEPART(YEAR, DATEADD(DAY, n*7 + 6, @FirstISOWKDay)) < @Year+2

--http://stackoverflow.com/questions/14133816/how-do-i-build-iso-week-number-table-programatically-in-t-sql-query



 
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