Date Dimension Stored procedure using a CTE

Have been playing around a little more making my time dimension creation and population a little more efficient

Let’s say we have a dimension table created like so

CREATE TABLE [dbo].[CALENDAR]( [CAL_DATE] [datetime] NULL, [CAL_DAY] [varchar](50) NULL, [CAL_DAY_IN_WEEK] [int] NULL, [CAL_DAY_IN_MONTH] [int] NULL, [CAL_DAY_OF_YEAR] [int] NULL, [CAL_WEEK] [int] NULL, [CAL_MONTH] [varchar](50) NULL, [CAL_SHORT_MONTH] [varchar](50) NULL, [CAL_MONTH_IN_YEAR] [int] NULL, [CAL_QUARTER] [int] NULL, [CAL_HALF_YEAR] [int] NULL, [CAL_YEAR] [int] NULL, [IS_WEEKEND] [int] NULL, [WORKING_DAY_NO] [int] NULL ) ON [PRIMARY] GO

We can then use a CTE to generate a list of data for this table efficiently (well, more efficiently than a loop)

Example: (generates data for 1 Jan 2010 to 31 Dec 2012).

WITH mycte as ( select cast('2010-01-01' as datetime) DateValue, case when datepart(dw, cast('2010-01-01' as datetime) -1) < 6 then 0 else 1 end IS_WEEKEND

union all select DateValue + 1, case when datepart(dw, DateValue) < 6 then 0 else 1 end IS_WEEKEND from mycte where DateValue + 1 < '2012-12-31' ) select DateValue as CAL_DATE , datename(dw, DateValue) CAL_DAY , datepart(dw, DateValue-1) CAL_DAY_IN_WEEK , datepart(dd, DateValue) CAL_DAY_IN_MONTH , datepart(dy, DateValue) CAL_DAY_OF_YEAR , datepart(ww, DateValue) CAL_WEEK , datename(mm, DateValue) CAL_MONTH , substring(datename(mm, DateValue), 1, 3) CAL_SHORT_MONTH , datepart(mm, DateValue) CAL_MONTH_IN_YEAR , datepart(qq, DateValue) CAL_QUARTER , case when datepart(mm, DateValue) < 7 then 1 else 2 end CAL_HALF_YEAR , datepart(yy, DateValue) CAL_YEAR , IS_WEEKEND , (SELECT sum(IS_WEEKEND) FROM mycte WHERE DateValue and datepart(yy, DateValue) = datepart(yy, m.DateValue) and datename(mm, DateValue) = datename(mm, m.DateValue) ) AS WORKING_DAY_NO FROM mycte m OPTION (MAXRECURSION 0)

So, to make this more efficient, how about wrapping this up in a stored procedure so we can just pass the start and end dates we need.

-- ================================================================================== -- Author: Will Riley (www.wills-blog.com | Twitter @troublewithdata) -- Create date: 2012-03-27 -- Description: Populate a date dimension table using a CTE based stored procedure -- -- Note: Use it like so:- -- -- INSERT INTO [dbo].[CALENDAR] -- execute MyDateGenerator '2010-01-01','2011-12-31' -- -- You also need to create the calendar table -- ==================================================================================

USE [TestDW]

GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N’[dbo].[MyDateGenerator]‘) AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)

BEGIN

DROP PROCEDURE [dbo].[MyDateGenerator]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[MyDateGenerator]

@FromDate datetime, @ToDate datetime

AS BEGIN

SET NOCOUNT ON;

WITH mycte as

(

select cast(@FromDate as datetime) DateValue,

case when datepart(dw, cast(@FromDate as datetime) -1) < 6 then 0 else 1 end IS_WEEKEND

union all

select DateValue + 1,

case when datepart(dw, DateValue) < 6 then 0 else 1 end IS_WEEKEND

from mycte

where DateValue + 1 < @ToDate

)

select DateValue as CAL_DATE

, datename(dw, DateValue) CAL_DAY

, datepart(dw, DateValue-1) CAL_DAY_IN_WEEK

, datepart(dd, DateValue) CAL_DAY_IN_MONTH

, datepart(dy, DateValue) CAL_DAY_OF_YEAR

, datepart(ww, DateValue) CAL_WEEK

, datename(mm, DateValue) CAL_MONTH

, substring(datename(mm, DateValue), 1, 3) CAL_SHORT_MONTH

, datepart(mm, DateValue) CAL_MONTH_IN_YEAR

, datepart(qq, DateValue) CAL_QUARTER

, case when datepart(mm, DateValue) < 7 then 1 else 2 end CAL_HALF_YEAR

, datepart(yy, DateValue) CAL_YEAR

, IS_WEEKEND

, (SELECT sum(IS_WEEKEND) FROM mycte

WHERE

DateValue and datepart(yy, DateValue) = datepart(yy, m.DateValue)

and datename(mm, DateValue) = datename(mm, m.DateValue)

) AS WORKING_DAY_NO

FROM mycte m

OPTION (MAXRECURSION 0)

END

GO

Now all we need to do is to run it like so:

INSERT INTO [dbo].[CALENDAR] execute MyDateGenerator '2010-01-01','2011-12-31'

I could imagine this being useful within an incremental SSIS package to add a new “chunk” of data to your time dimension…