Populating your date dimension

Posted on October 9, 2011

4



My friend and colleague Boyan Penev wrote a post recently on his blog regarding what a date dimension table should contain: http://www.bp-msbi.com/2011/09/building-a-date-table/

I’ve got a script that I’ve been using for a while to populate a date dimension table that I thought worth posting. I’ve not included fiscal periods in this script. Living in Australia this is usually quite simple to build since our Financial period starts on July 1st for most companies.

I believe the code is fairly self-explanatory, and Boyan has covered the essentials.

Here is what the date dimension will look like:

Here’s the code to populate it. I hope you find it useful.

IF OBJECT_ID('DimDate') IS NOT NULL DROP TABLE DimDate
GO
CREATE TABLE [dbo].[DimDate](
[DateKey] [int] NOT NULL,
[CalendarDate] [date] NOT NULL,
[CalendarYear] [int] NOT NULL,
[CalendarHalf] [char](5) NOT NULL,
[CalendarHalfNumber] [int] NOT NULL,
[CalendarYearHalfName] [varchar](39) NOT NULL,
[CalendarQuarter] [char](5) NOT NULL,
[CalendarQuarterNumber] [int] NOT NULL,
[CalendarYearQuarterName] [varchar](65) NOT NULL,
[CalendarMonth] [int] NOT NULL,
[CalendarMonthNumber] [int] NOT NULL,
[CalendarMonthName] [varchar](30) NOT NULL,
[CalendarYearMonthName] [varchar](35) NOT NULL,
[CalendarMonthStart] [int] NOT NULL,
[CalendarMonthEnd] [int] NOT NULL,
[CalendarWeek] [int] NOT NULL,
[WeekDay] [int] NOT NULL,
[WeekDayName] [varchar](30) NOT NULL,
[WeekDayType] [varchar](7) NOT NULL,
[DaysInCalendarYear] [int] NOT NULL,
[DaysInCalendarMonth] [int] NOT NULL,
[DaysInCalendarWeek] [int] NOT NULL,
[WeeksInCalendarMonth] [int] NOT NULL,
[WeeksInCalendarYear] [int] NOT NULL,
CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX UIX_DimDate ON DimDate(CalendarDate)
GO
SET DATEFIRST 1 -- If first day of week is a Monday. If first day of week = Saturday then set to 6
GO
--======================================
-- Populate the dimension
--======================================
;WITH DateCTE AS
(
SELECT CAST('2010-01-01' AS DATE) AS DateValue
UNION ALL
SELECT DATEADD(d,1,DateValue)
FROM DateCTE
WHERE DATEADD(d,1,DateValue) < '2015-01-01'
)
INSERT INTO DimDate
SELECT
CAST(CONVERT(CHAR(8),CAST(DateValue AS DATETIME),112) AS INT) AS DateKey
, DateValue AS CalendarDate
, YEAR(DateValue) AS CalendarYear
, CASE
WHEN DATEPART(Quarter ,DateValue) < 3 THEN
CAST(YEAR(DateValue) AS CHAR(4)) + '1'
ELSE
CAST(YEAR(DateValue) AS CHAR(4)) + '2'
END AS CalendarHalf
, CASE
WHEN DATEPART(Quarter ,DateValue) < 3 THEN
1
ELSE
2
END AS CalendarHalfNumber
, CASE
WHEN DATEPART(Quarter ,DateValue) < 3 THEN
DATENAME(YEAR,DateValue) + ' 1st Half'
ELSE
DATENAME(YEAR,DateValue) + ' 2nd Half'
END AS CalendarYearHalfName
, CAST(YEAR(DateValue) AS CHAR(4)) + CAST(DATEPART(Quarter ,DateValue) AS CHAR(1)) AS CalendarQuarter
, DATEPART(Quarter ,DateValue) as CalendarQuarterNumber
, DATENAME(YEAR,DateValue) + ' Qtr' + DATENAME(QUARTER,DateValue) AS CalendarYearQuarterName
, CAST(CONVERT(CHAR(6),CAST(DateValue AS DATETIME),112) AS INT) AS CalendarMonth
, DATEPART(m,DateValue) AS CalendarMonthNumber
, DATENAME(MONTH,DateValue) AS CalendarMonthName
, CAST(YEAR(DateValue) AS CHAR(4)) + ' ' + DATENAME(MONTH,DateValue) AS CalendarYearMonthName
, CONVERT(CHAR(6),CAST(DateValue AS DATETIME),112) + '01' AS CalendarMonthStart
, CAST(CONVERT(CHAR(8),CAST(CONVERT(VARCHAR(12), dateadd(day,-1 * day(dateadd(month,1,DateValue)),dateadd(month,1,DateValue)),113) AS DATETIME),112) AS INT) AS CalendarMonthEnd
, CAST(YEAR(DateValue) AS CHAR(4)) + CAST(DATEPART(wk ,DateValue) AS VARCHAR(2)) AS CalendarWeek
, DATEPART(dw, DateValue) AS WeekDay
, DATENAME(dw, DateValue) AS WeekDayName
, CASE
WHEN DATENAME(dw, DateValue) IN ('Saturday','Sunday') THEN
'WeekEnd'
ELSE
'WeekDay'
END AS WeekDayType
, COUNT(DATEPART(wk ,DateValue)) OVER (PARTITION BY YEAR(DateValue)) AS DaysInCalendarYear
, COUNT(*) OVER (PARTITION BY CAST(CONVERT(CHAR(6),CAST(DateValue AS DATETIME),112) AS INT)) AS DaysInCalendarMonth
, COUNT(*) OVER (PARTITION BY CAST(YEAR(DateValue) AS CHAR(4)) + CAST(DATEPART(wk ,DateValue) AS VARCHAR(2)) ) AS DaysInCalendarWeek
, (SELECT DISTINCT
COUNT(DATEPART(wk,b.DateValue)) OVER (PARTITION BY DATEPART(m,b.DateValue)) AS WeeksInCalendarMonth
FROM DateCTE b
WHERE DATEPART(m,a.DateValue) = DATEPART(m,b.DateValue)
AND YEAR(a.DateValue) = YEAR(b.DateValue)
AND DATEPART(dw, DateValue) = (1)
) AS WeeksInCalendarMonth
, (SELECT DISTINCT
COUNT(DATEPART(wk ,DateValue)) OVER (PARTITION BY YEAR(DateValue)) AS WeeksInCalendarYear
FROM DateCTE b
WHERE YEAR(a.DateValue) = YEAR(b.DateValue)
AND DATEPART(dw, DateValue) = (1)
) AS WeeksInCalendarYear
FROM DateCTE a
ORDER BY DateKey
OPTION (MAXRECURSION 5000)
GO
--======================================
-- Populate default low and high values
--======================================
;WITH DateCTE AS
(
SELECT CAST('1900-01-01' AS DATE) AS DateValue
UNION ALL
SELECT CAST('2999-12-31' AS DATE)
)
INSERT INTO DimDate
SELECT
CAST(CONVERT(CHAR(8),CAST(DateValue AS DATETIME),112) AS INT) AS DateKey
, DateValue AS CalendarDate
, YEAR(DateValue) AS CalendarYear
, CASE
WHEN DATEPART(Quarter ,DateValue) < 3 THEN
CAST(YEAR(DateValue) AS CHAR(4)) + '1'
ELSE
CAST(YEAR(DateValue) AS CHAR(4)) + '2'
END AS CalendarHalf
, CASE
WHEN DATEPART(Quarter ,DateValue) < 3 THEN
1
ELSE
2
END AS CalendarHalfNumber
, CASE
WHEN DATEPART(Quarter ,DateValue) < 3 THEN
DATENAME(YEAR,DateValue) + ' 1st Half'
ELSE
DATENAME(YEAR,DateValue) + ' 2nd Half'
END AS CalendarYearHalfName
, CAST(YEAR(DateValue) AS CHAR(4)) + CAST(DATEPART(Quarter ,DateValue) AS CHAR(1)) AS CalendarQuarter
, DATEPART(Quarter ,DateValue) as CalendarQuarterNumber
, DATENAME(YEAR,DateValue) + ' Qtr' + DATENAME(QUARTER,DateValue) AS CalendarYearQuarterName
, CAST(CONVERT(CHAR(6),CAST(DateValue AS DATETIME),112) AS INT) AS CalendarMonth
, DATEPART(m,DateValue) AS CalendarMonthNumber
, DATENAME(MONTH,DateValue) AS CalendarMonthName
, CAST(YEAR(DateValue) AS CHAR(4)) + ' ' + DATENAME(MONTH,DateValue) AS CalendarYearMonthName
, CONVERT(CHAR(6),CAST(DateValue AS DATETIME),112) + '01' AS CalendarMonthStart
, CAST(CONVERT(CHAR(8),CAST(CONVERT(VARCHAR(12), dateadd(day,-1 * day(dateadd(month,1,DateValue)),dateadd(month,1,DateValue)),113) AS DATETIME),112) AS INT) AS CalendarMonthEnd
, CAST(YEAR(DateValue) AS CHAR(4)) + CAST(DATEPART(wk ,DateValue) AS VARCHAR(2)) AS CalendarWeek
, DATEPART(dw, DateValue) AS WeekDay
, DATENAME(dw, DateValue) AS WeekDayName
, CASE
WHEN DATENAME(dw, DateValue) IN ('Saturday','Sunday') THEN
'WeekEnd'
ELSE
'WeekDay'
END AS WeekDayType
, COUNT(DATEPART(wk ,DateValue)) OVER (PARTITION BY YEAR(DateValue)) AS DaysInCalendarYear
, COUNT(*) OVER (PARTITION BY CAST(CONVERT(CHAR(6),CAST(DateValue AS DATETIME),112) AS INT)) AS DaysInCalendarMonth
, COUNT(*) OVER (PARTITION BY CAST(YEAR(DateValue) AS CHAR(4)) + CAST(DATEPART(wk ,DateValue) AS VARCHAR(2)) ) AS DaysInCalendarWeek
, CASE WHEN DateValue = '1900-01-01' THEN 4 ELSE 5 END AS WeeksInCalendarMonth
, CASE WHEN DateValue = '1900-01-01' THEN 52 ELSE 53 END AS WeeksInCalendarYear
FROM DateCTE a
GO

Advertisements
Posted in: Data Modeling, SQL