Sprocs to create Date and Time Dimensions
May 19, 2010 1 Comment
Working a project and needed generic date and time dimensions, wrote the following stored procedures, blogged here for future use…
The date one accepts 2 parameters a start date and an end date, it then creates a row for each day between these dates.
We had a bit of a discussion on the time one about whether it should go to the minute or second level so I’ve included a parameter called @grain which should be set to either ‘second’ or ‘minute’, so you can choose which level you need.
SEE COMMENTS FOR TIME DIMENSION SPROC…
USE[DanDev]
GO
/****** Object: StoredProcedure [dbo].[PopulateDimDate] Script Date: 05/19/2010 17:13:24 ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEprocedure [dbo].[PopulateDimDate] (@start asdatetime, @end asdatetime)
as
declare @dte asdatetime
— run the below statement to test
— exec PopulateDimDate ’01 Jan 2000′, ’31 Dec 2049′
— 150 years worth takes 22 seconds to create 54,787 rows
— either create or truncate the table
ifobject_id(‘DimDate’)isnull
begin
CREATE TABLE [dbo].[DimDate]
(
[DateKey] [int] NULL,
[CalDate] [datetime] NULL,
[TextDateShort] [nvarchar](6) NULL,
[TextDateLong] [nvarchar](11) NULL,
[CalWeek] [int] NULL,
[CalMonthNum] [int] NULL,
[CalQtr] [int] NULL,
[CalYear] [int] NULL,
[IsWeekend] [bit] NULL,
[DoW] [nvarchar](9) NULL,
[DoM] [int] NULL,
[CalMonthName] [nvarchar](9) NULL,
[IsFirstDayOfMonth] [bit] NULL,
[IsLastDayOfMonth] [bit] NULL,
[IsLeapYear] [bit] NULL
)
end
else
truncate table dbo.DimDate
— use while to create all rows with just date field populated to keep it quick as poss
set @dte = @start
while @dte <= @end
begin
insert into DimDate (CalDate) values (@dte)
set @dte = dateadd(dd,1,@dte)
end
— Now populate all additional fields
update DimDate
set DateKey = year(CalDate)*10000+month(CalDate)*100+day(CalDate),
TextDateShort = left(convert(varchar(16),CalDate,113),6),
TextDateLong = left(convert(varchar(16),CalDate,113),11),
CalWeek = datepart(week,CalDate),
CalMonthNum = month(CalDate),
CalQtr = (month(CalDate)/4)+1,
CalYear = year(CalDate),
IsWeekend = case when datename(dw,CalDate) in(‘Saturday’,‘Sunday’) then ‘TRUE’ else ‘FALSE’ end,
DoW = datename(dw,CalDate),
DoM = day(CalDate),
CalMonthName = datename(month,CalDate),
IsFirstDayOfMonth = case when day(CalDate) = 1 then ‘TRUE’ else ‘FALSE’ end,
IsLastDayOfMonth = case when day(dateadd(dd,1,CalDate)) = 1 then ‘TRUE’ else ‘FALSE’ end,
IsLeapYear = case when year(CalDate) % 4 = 0
and (year(CalDate) % 100 <> 0
or year(CalDate) % 400 = 0) then ‘TRUE’ else ‘FALSE’ end
About Dan Wakefield
Dan's BI Braindump 
USE [DanDev]GO/****** Object: StoredProcedure [dbo].[PopulateDimTime] Script Date: 05/19/2010 17:13:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo].[PopulateDimTime] (@grain as varchar(6))as– @grain is either \’minute\’ or \’second\’declare @time datetime– test– exec PopulateDimTime \’turd\’– 20 seconds to create at second grain, less than a second at minute grainif @grain not in(\’second\’,\’minute\’)beginprint \’Please enter second or minute in @grain parameter\’returnendif @grain = \’second\’begin– either create or truncate the table– if prev created at only minute level then need to drop and recreateif not exists (select * from INFORMATION_SCHEMA.COLUMNSwhere TABLE_NAME = \’DimTime\’ and COLUMN_NAME = \’NumSecond\’)if object_id(\’DimTime\’) is null drop table DimTimeif object_id(\’DimTime\’) is nullbeginCREATE TABLE DimTime(TimeKey int NULL,TimeValue datetime NULL,TextTime nchar(8) NULL,Intraday nchar(5) NULL,AmPm nchar(2) NULL,TextHour nchar(2) NULL,TextMinute nchar(2) NULL,TextSecond nchar(2) NULL,NumHour int NULL,NumMinute int NULL,NumSecond int NULL)endelsetruncate table DimTimeset @time = \’00:00:00\’– use while to create all rows with just date field populated to keep it quick as posswhile @time <= \’23:59:59\’begininsert into DimTime (TimeValue) values (@time)set @time = dateadd(second,1,@time)end– Now populate all additional fieldsupdate DimTimeset TimeKey = datepart(hour,TimeValue)*10000+datepart(minute,TimeValue)*100+datepart(second,TimeValue),TextTime = convert(varchar(8),TimeValue,114),Intraday = replace(convert(varchar(2),TimeValue,114)+\’:\’+cast(datepart(minute,TimeValue)/15*15 as char(2)),\’:0\’,\’:00\’),AmPm = case when datepart(hour,TimeValue) < 12 then \’Am\’ else \’Pm\’ end,TextHour = convert(varchar(2),TimeValue,114),TextMinute = substring(convert(varchar(8),TimeValue,114),4,2),TextSecond = right(convert(varchar(8),TimeValue,114),2),NumHour = datepart(hour,TimeValue),NumMinute = datepart(minute,TimeValue),NumSecond = datepart(second,TimeValue)end — end of \’second\’ grain processingelsebegin– either create or truncate the table– if prev created at second level then need to drop and recreateif exists (select * from INFORMATION_SCHEMA.COLUMNSwhere TABLE_NAME = \’DimTime\’ and COLUMN_NAME = \’NumSecond\’)if object_id(\’DimTime\’) is null drop table DimTimeif object_id(\’DimTime\’) is nullbeginCREATE TABLE DimTime(TimeKey int NULL,TimeValue datetime NULL,TextTime nchar(5) NULL,Intraday nchar(5) NULL,AmPm nchar(2) NULL,TextHour nchar(2) NULL,TextMinute nchar(2) NULL,NumHour int NULL,NumMinute int NULL)endelsetruncate table DimTimeset @time = \’00:00:00\’– use while to create all rows with just date field populated to keep it quick as posswhile @time <= \’23:59:59\’begininsert into DimTime (TimeValue) values (@time)set @time = dateadd(minute,1,@time)end– Now populate all additional fieldsupdate DimTimeset TimeKey = datepart(hour,TimeValue)*10000+datepart(minute,TimeValue)*100+datepart(second,TimeValue),TextTime = convert(varchar(5),TimeValue,114),Intraday = replace(convert(varchar(2),TimeValue,114)+\’:\’+cast(datepart(minute,TimeValue)/15*15 as char(2)),\’:0\’,\’:00\’),AmPm = case when datepart(hour,TimeValue) < 12 then \’Am\’ else \’Pm\’ end,TextHour = convert(varchar(2),TimeValue,114),TextMinute = substring(convert(varchar(5),TimeValue,114),4,2),NumHour = datepart(hour,TimeValue),NumMinute = datepart(minute,TimeValue)end — end of \’minute\’ grain processing