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
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…
