Date Dimension Stored procedure using a CTE

I Have been playing around recently to make 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
) 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 1 
   ELSE 0 
END AS IS_WORKDAY

UNION ALL

SELECT DateValue + 1,
CASE WHEN DATEPART(dw, DateValue) < 6 
   THEN 1 
   ELSE 0
END AS IS_WORKDAY
FROM mycte
WHERE DateValue + 1 < '2012-12-31')

SELECT DateValue AS CAL_DATE
, DATENAME(dw, DateValue) AS CAL_DAY
, DATEPART(dw, DateValue-1) AS CAL_DAY_IN_WEEK
, DATEPART(dd, DateValue) AS CAL_DAY_IN_MONTH
, DATEPART(dy, DateValue) AS CAL_DAY_OF_YEAR
, DATEPART(ww, DateValue) AS CAL_WEEK
, DATENAME(mm, DateValue) AS CAL_MONTH
, SUBSTRING(DATENAME(mm, DateValue), 1, 3) AS CAL_SHORT_MONTH
, DATEPART(mm, DateValue) AS CAL_MONTH_IN_YEAR
, DATEPART(qq, DateValue) AS CAL_QUARTER
, CASE WHEN DATEPART(mm, DateValue) < 7 THEN 1 ELSE 2 END AS CAL_HALF_YEAR
, DATEPART(yy, DateValue) AS CAL_YEAR
, IS_WORKDAY
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.datasapien.co.uk | Twitter @Datasapien)
-- 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
-- ==================================================================================</code>

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 1 
   ELSE 0 
END AS IS_WORKDAY
UNION ALL
SELECT DateValue + 1,
CASE WHEN DATEPART(dw, DateValue) < 6 
   THEN 1 
   ELSE 0
END AS IS_WORKDAY
FROM mycte
WHERE DateValue + 1 < @ToDate
)

SELECT DateValue AS CAL_DATE
, DATENAME(dw, DateValue) AS CAL_DAY
, DATEPART(dw, DateValue-1) AS CAL_DAY_IN_WEEK
, DATEPART(dd, DateValue) AS CAL_DAY_IN_MONTH
, DATEPART(dy, DateValue) AS CAL_DAY_OF_YEAR
, DATEPART(ww, DateValue) AS CAL_WEEK
, DATENAME(mm, DateValue) AS CAL_MONTH
, SUBSTRING(DATENAME(mm, DateValue), 1, 3) AS CAL_SHORT_MONTH
, DATEPART(mm, DateValue) AS CAL_MONTH_IN_YEAR
, DATEPART(qq, DateValue) AS CAL_QUARTER
, CASE WHEN DATEPART(mm, DateValue) < 7 THEN 1 ELSE 2 END AS CAL_HALF_YEAR
, DATEPART(yy, DateValue) AS CAL_YEAR
, IS_WORKDAY

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…