Create and Populate Time Dimension

CREATE TABLE [dbo].[DateDim] (
[DateKey] [int] IDENTITY (1, 1) NOT NULL ,
[ActualDate] [datetime] NOT NULL ,
[Year] [int] NOT NULL ,
[Quarter] [int] NOT NULL ,
[Month] [int] NOT NULL ,
[Week] [int] NOT NULL ,
[DayofYear] [int] NOT NULL ,
[DayofMonth] [int] NOT NULL ,
[DayofWeek] [int] NOT NULL ,
[IsWeekend] [bit] NOT NULL ,
[IsHoliday] [bit] NOT NULL ,
[Comments] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
[CalendarWeek] [int] NOT NULL ,
[BusinessYearWeek] [int] NOT NULL ,
[LeapYear] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

CREATE PROCEDURE sp_createTimeDim  AS

--delete contents of Date Dimension Table
TRUNCATE TABLE DateDim

--declare variables
DECLARE @DT DATETIME
DECLARE @YEAR INT
DECLARE @QUARTER INT
DECLARE @MONTH  INT
DECLARE @WEEK  INT
DECLARE @DayofYear INT
DECLARE @DayofMonth INT
DECLARE @DayofWeek INT
DECLARE @IsWeekend  BIT
DECLARE @IsHoliday  BIT
DECLARE @CalendarWeek INT
DECLARE @DayName VARCHAR(20)
DECLARE @MonthName VARCHAR(20)
DECLARE @BusinessYearWeek INT
DECLARE @LeapYear BIT

--initialize variables

SELECT @BusinessYearWeek =0
SELECT @CalendarWeek = 1
SELECT @LeapYear =0

--the starting date for the date dimension
SELECT @DT  = '1/1/1998'

--start looping, stop at ending date
WHILE (@DT <= '1/31/2005')
BEGIN

--get information about the data
SELECT @IsWeekend  =0
SELECT @YEAR = DATEPART (YEAR, @DT)
SELECT @QUARTER = DATEPART (QUARTER, @DT) 
SELECT @MONTH = DATEPART (MONTH , @DT)
SELECT @WEEK  = DATEPART (WEEK , @DT)
SELECT @DayofYear   = DATEPART (DY , @DT)
SELECT @DayofMonth   = DATEPART (DAY , @DT)
SELECT @DayofWeek   = DATEPART (DW , @DT)

--note if weeknd or not
IF ( @DayofWeek = 1 OR  @DayofWeek = 7 )  
BEGIN
SELECT @IsWeekend   = 1
END

--add 1 every time we start a new week
IF ( @DayofWeek = 1)
BEGIN
SELECT @CalendarWeek = @CalendarWeek +1
END

--add business rule (need to know complete weeks in a year, so a partial week in new year set to 0)
IF ( @DayofWeek != 1 AND @DayofYear = 1)
BEGIN
SELECT @BusinessYearWeek = 0
END


IF ( @DayofWeek = 1)
BEGIN
SELECT @BusinessYearWeek = @BusinessYearWeek +1
END

--add business rule (start counting business weeks with first complete week)
IF (@BusinessYearWeek =53)
BEGIN
SELECT @BusinessYearWeek = 1
END

--check for leap year
IF ((@YEAR % 4 = 0)  AND (@YEAR % 100 != 0 OR @YEAR % 400 = 0))
SELECT @LeapYear =1
ELSE SELECT @LeapYear =0

--insert values into Date Dimension table

INSERT DateDim  (ActualDate, Year, Quarter, Month, Week, DayofYear, DayofMonth, DayofWeek, IsWeekend, CalendarWeek, BusinessYearWeek, LeapYear)
 VALUES (@DT, @YEAR, @QUARTER, @MONTH, @WEEK, @DayofYear, @DayofMonth, @DayofWeek, @IsWeekend, @CalendarWeek, @BusinessYearWeek, @LeapYear)

--increment the date one day
SELECT @DT  = DATEADD(DAY, 1, @DT)

END
GO