The scripts to populate them would be.....--=============================================
-- Author: Robin K
-- Create date: 27-January-2009
-- Description: Used to Insert Date Dimension Records
-- =============================================
CREATE PROCEDURE [dbo].[pcins_DateDimension]
@StartDate DATETIME = '1/1/2007'
,@EndDate DATETIME = '12/31/2011'
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM DimDate;
DECLARE @TempDimDate TABLE
(
Date DATETIME
,DayNumberOfWeek TINYINT
,DayNameOfWeek VARCHAR(10)
,DayNumberOfMonth TINYINT
,DayNumberOfYear SMALLINT
,WeekNumberOfYear TINYINT
,MonthName VARCHAR(10)
,MonthNumberOfYear TINYINT
,CalendarQuarter TINYINT
,CalendarYear CHAR(4)
,CalendarSemester TINYINT
);
-- USE CTE to create our date iterator
WITH CalculatedDate(Date) AS
(
SELECT
@StartDate Date
UNION ALL
SELECT
(Date + 1)
FROM
CalculatedDate
WHERE
((Date + 1) <= @EndDate)
)
--insert each date to the temporay dimension table
INSERT INTO @TempDimDate
SELECT Date AS Date
,DATEPART(DW,Date) AS DayNumberOfWeek
,DATENAME(DW,Date) AS DayNameOfWeek
,DAY(Date) AS DayNumberOfMonth
,DATEPART(DY,Date) AS DayNumberOfYear
,DATEPART(WK,Date) AS WeekNumberOfYear
,DATENAME(MM,Date) AS MonthName
,Month(Date) AS MonthNumberOfYear
,DATENAME(QUARTER, Date) AS CalendarQuarter
,YEAR(Date) AS CalendarYear
,CASE WHEN DATEPART(mm, Date) > 6 THEN 2
ELSE 1
END AS CalendarSemester
FROM CalculatedDate
OPTION (MAXRECURSION 0)
INSERT INTO DimDate (Date
,DayNumberOfWeek
,DayNameOfWeek
,DayNumberOfMonth
,DayNumberOfYear
,WeekNumberOfYear
,MonthName
,MonthNumberOfYear
,CalendarQuarter
,CalendarYear
,CalendarSemester
)
SELECT Date
,DayNumberOfWeek
,DayNameOfWeek
,DayNumberOfMonth
,DayNumberOfYear
,WeekNumberOfYear
,MonthName
,MonthNumberOfYear
,CalendarQuarter
,CalendarYear
,CalendarSemester
FROM @TempDimDate
END
