Our Articles

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

A time dimension is something you should not have to create on a nightly or incremental basis during execution of the existing data warehouse ETL.  Instead, here is some SQL to generate a time dimension for you.  Modify it as needed to suit your needs.

/*
Author/Co-author:Lawrence Patrick
Email: [email protected]
Object: SQL code; none
Description: Create a time dimension
Usage: run script and edit code to insert to a DimDate.
Returns: nothing
Example: n/a
Created: 11/14/2006
Last Updated: 9/10/2007
Notes: This code generates dates from 1980 to 2050
*/

SET
DATEFIRST 1 ;
–SET WHAT DAY OF WEEK IS CONSIDERED FIRST DAY ie. Sunday, Monday
WITH tmpDates
AS ( SELECT CAST(’1980-01-01′ AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM tmpDates
WHERE DateValue + 1 < ’2050-12-31′
)
SELECT row_number() OVER ( ORDER BY DateValue ASC ) AS RowNumber,
DateValue,
CONVERT(NVARCHAR(30), DateValue, 112) AS ‘DateKey’,
CONVERT(NVARCHAR(30), DateValue, 101) AS ‘FullDate’,
CONVERT(NVARCHAR(30), DateValue, 111) AS ‘DateName’,
DAY(DateValue) AS ‘DayOfWeek’,
DATENAME(weekday, DateValue) AS ‘DayNameOfWeek’,
DATEPART(m, DateValue) AS ‘DayOfMonth’,
DATEPART(dy, DateValue) AS ‘DayOfYear’,
( CASE DATEPART(dw, DateValue)
WHEN 6 THEN ‘Weekend’
WHEN 7 THEN ‘Weekend’
ELSE ‘Weekday’
END ) AS ‘WeekdayWeekend’,
DATEPART(wk, DateValue) AS ‘WeekOfYear’,
DATENAME(month, DateValue) AS ‘MonthName’,
DATEPART(mm, DateValue) AS ‘MonthOfYear’,
DATEPART(qq, DateValue) AS ‘CalendarQuarter’,
YEAR(DateValue) AS ‘CalendarYear’,
CAST(DATEPART(yy, DateValue) AS NVARCHAR) + ‘-’
+ SUBSTRING(CONVERT(NVARCHAR(30), DateValue, 112), 5, 2) AS ‘CalendarYearMonth’,
CAST(YEAR(DateValue) AS NVARCHAR) + ‘Q’
+ CAST(DATEPART(Quarter, DateValue) AS NVARCHAR) AS ‘CalendarYearQtr’,
( CASE DATEPART(mm, DateValue)
WHEN 1 THEN ’7′
WHEN 2 THEN ’8′
WHEN 3 THEN ’9′
WHEN 4 THEN ’10′
WHEN 5 THEN ’11′
WHEN 6 THEN ’12′
WHEN 7 THEN ’1′
WHEN 8 THEN ’2′
WHEN 9 THEN ’3′
WHEN 10 THEN ’4′
WHEN 11 THEN ’5′
ELSE ’6′
END ) AS ‘FiscalMonthOfYear’,
( CASE MONTH(DateValue)
WHEN 1 THEN YEAR(DateValue)
WHEN 2 THEN YEAR(DateValue)
WHEN 3 THEN YEAR(DateValue)
WHEN 4 THEN YEAR(DateValue)
WHEN 5 THEN YEAR(DateValue)
WHEN 6 THEN YEAR(DateValue)
ELSE CAST(( YEAR(DateValue) + 1 ) AS NVARCHAR)
END ) AS ‘FiscalYear’
/*–Place name of date dimension here once you
are satisfied with the results.*/
–INTO DimDate
FROM tmpDates
OPTION ( MAXRECURSION 0 )

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Lawrence Patrick

www.lawrencepatrick.net

So, what do you think ?

You must be logged in to post a comment.