SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Script to Populate Date Dimension, without Using a Cursor

Most of the scripts I’ve used to populate date dimension uses a cursor. Since data is loaded only once to a date dimension in the ETL life cycle, using a cursor isn’t a sin.

Still, when I was reviewing my own code the other day, I wanted to get rid of the cursor. Why not, Right?

Here is a script that uses CTE and Window Functions to populate the date dimension.

DECLARE @startdate DATE = '20000101'
	, @enddate DATE = '20301231' ;

AS (
	FROM sys.columns c
	CROSS JOIN sys.columns c1
, d
AS (
	SELECT	[date] = DATEADD(day, Num, @startdate)
			, Num
	WHERE Num >= 0
		AND Num <= DATEDIFF(day, @startdate, @enddate)
SELECT datekey = CAST(CONVERT(VARCHAR(8), DATEADD(day, Num, @startdate), 112) AS INT)
	, [date]
	, [DayOfMonth] = DATEPART(day, [Date])
	, [DayName] = DATENAME(weekday, [Date])
	, [DayOfYear] = DATEPART(dayofyear, [Date])
	, [WeekOfYear] = DATEPART(week, [Date])
	, [MonthName] = DATENAME(month, [Date])
	, [MonthNumber] = DATEPART(month, [Date])
	, [QuarterNumber] = DATEPART(quarter, [Date])
	, [Year] = YEAR([date])
	, [FiscalYear] = CASE 
		WHEN DATEPART(month, [Date]) < 7
			THEN YEAR([date])
		ELSE YEAR([date]) + 1
--Add more columns as needed.

Let’s take a closer look:

  1. Variables StartDate and EndDate hold the date range for the result set.
  2. We define a CTE (called c) that uses Row_Number() function to generate numbers starting from zero for each row in sys.columns. sys.columns is cross joined to itself to generate more number of [input] rows.
  3. We define another CTE (called d) that uses numbers from c, filters on date range, and converts numbers to dates based on the date variables.
  4. Finally, the Select statement uses Num and Date column aliases created by the two CTEs and date functions to create additional fields for the date dimension.

You’ll notice that the script doesn’t have all columns a date dimension typically has. Don’t call me lazy for that – it provides the logic and skeleton to add more columns. Just add them as needed.

Sam Vanga

Sam Vanga helps business people solve their data problems and enables them make data-driven-decisions. His specialties include data integration, data warehouse and database development, and BI design. Sam blogs at http://SamuelVanga.com.


Leave a comment on the original post [blogs.lessthandot.com, opens in a new window]

Loading comments...