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