CREATE DIMDATE TABLE SCRIPT
/****** Object: Table [dbo].[DimDate] Script Date: 08/17/2009 10:58:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[DimDate](
[DateKey] [int] IDENTITY(1,1)
NOT NULL,
[Date] [smalldatetime] NOT NULL,
[DayOfMonth] [int] NOT NULL,
[DayOfYear] [int] NOT NULL,
[WeekOfMonth] [int] NOT NULL,
[WeekOfYear] [int] NOT NULL,
[WeekBeginDate] [smalldatetime] NULL,
[WeekEndDate] [smalldatetime] NULL,
[DayName] [varchar](50)
NOT NULL,
[FiscalMonthNumber] [int] NULL,
[FiscalMonthName] [varchar](50)
NULL,
[MonthNumber] [int] NOT NULL,
[MonthName] [varchar](50)
NOT NULL,
[Year] [int] NOT NULL,
[FiscalYear] [int] NULL,
[Semester] [varchar](50)
NOT NULL,
[Quarter] [int] NOT NULL,
[QuarterName] [varchar](50)
NULL,
[FiscalQuarter] [int] NULL,
[FiscalQuarterName] [varchar](50)
NULL,
[Is_Holiday] [tinyint] NULL,
[IsBusinessDay] [tinyint] NULL,
[IsWeekEnd] [tinyint] NULL,
[IS Prior Date] [tinyint] NULL,
[Is Fiscal] [int] NULL,
CONSTRAINT [DimDate_PK] PRIMARY
KEY
CLUSTERED
(
[Date] ASC
)WITH
(PAD_INDEX
=
OFF,
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING
OFF
GO
CREATE INSERT Stored Proc's Script
ALTER
PROCEDURE [dbo].[pcins_DateDimension]
@StartDate DATETIME
=
'1/1/2005'
,@EndDate DATETIME
=
'1/1/2011'
AS
BEGIN
SET
NOCOUNT
ON;
DELETE
FROM DimDate;
DECLARE @TempDimDate TABLE
(
Date DATETIME
,DayOfMonth INT
,DayOfYear INT
,WeekOfMonth INT
,WeekOfYear INT
,DayName NVARCHAR(10)
,MonthName NVARCHAR(10)
,MonthNumber INT
,Year INT
,[Quarter] INT
,Semester INT
,Is_Holiday INT NULL
);
-- 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
, DAY(Date)
AS DayOfMonth
,
DATEPART(dy,Date) [DayOfYear]
,
DATEPART(WEEK, Date)
-
DATEPART(WEEK,
DATEADD(month,
DATEDIFF(month, 0, Date), 0))
+ 1 AS WeekOfMonth
,
DATEPART(wk,Date) [WeekOfYear] --DATEPART(wk,Date)
,
DATENAME(dw,Date)
AS
DayName
,
DATENAME(mm,Date)
AS
MonthName
,
Month(Date)
AS [MonthNumber]
,
YEAR(Date)
AS
Year
,
DATEPART(qq, Date)
Quarter
,
CASE WHEN DATEPART(mm, Date)
> 6 THEN 2
ELSE 1
END Semester
,0 AS Is_Holiday
FROM
CalculatedDate
OPTION
(MAXRECURSION 0)
INSERT INTO DimDate (Date
,DayOfMonth
,DayOfYear
,WeekOfMonth
,WeekOfYear
,DayName
,MonthName
,MonthNumber
,Year
,Quarter
,Semester
,Is_Holiday
)
SELECT Date
,DayOfMonth
,DayOfYear
,WeekOfMonth
,WeekOfYear
,DayName
,MonthName
,MonthNumber
,Year
,Quarter
,Semester
,Is_Holiday
FROM @TempDimDate
UPDATE DimDate SET DimDate.WeekBeginDate = WeekBeginEndDate.WeekBeginDate
,DimDate.WeekEndDate = WeekBeginEndDate.WeekEndDate
FROM DimDate
JOIN
(SELECT MIN(date)
AS WeekBeginDate
,MAX(date)
AS WeekEndDate
,[Year]
,[WeekOfYear]
FROM DimDate
GROUP
BY [Year]
,[WeekOfYear]
)
AS WeekBeginEndDate
ON DimDate.[Year] = WeekBeginEndDate.[Year]
AND DimDate.[WeekOfYear] = WeekBeginEndDate.[WeekOfYear]
UPDATE DimDate SET IsBusinessDay =
CASE WHEN
DATEPART(DW,Date)
NOT IN
(1,7)
THEN 1
ELSE 0
END
,IsWeekEnd = CASE WHEN
DATEPART(DW,Date)
IN
(1,7)
THEN 1
ELSE 0
END
FROM DimDate
UPDATE DimDate
SET FiscalYear =
CASE
WHEN
MONTH(Date)
>= 4 THEN
YEAR(Date)
ELSE
YEAR(Date)
- 1 END
FROM DimDate
-- To Update FiscalMonth
UPDATE DimDate
SET FiscalMonthNumber
=
CASE MonthNumber
WHEN 10 THEN 7
WHEN 11 THEN 8
WHEN 12 THEN 9
WHEN 1 THEN 10
WHEN 2 THEN 11
WHEN 3 THEN 12
WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 3
WHEN 7 THEN 4
WHEN 8 THEN 5
WHEN 9 THEN 6
END
-- To Update FiscalQuarter
UPDATE DimDate
SET FiscalQuarter
=
CASE
WHEN FiscalMonthNumber IN
(1,2,3)
THEN 1
WHEN FiscalMonthNumber IN
(4,5,6)
THEN 2
WHEN FiscalMonthNumber IN
(7,8,9)
THEN 3
WHEN FiscalMonthNumber IN
(10,11,12)
THEN 4
END
-- To Update FiscalQuarterName
UPDATE DimDate
SET FiscalQuarterName =
'Quarter - '
+
CAST(FiscalQuarter AS
VARCHAR(1))
UPDATE DimDate
SET QuarterName =
'Quarter - '
+
CAST(Quarter
AS
VARCHAR(1))
SET
NOCOUNT
OFF;
END
GO
UPDATE Fiscal Year, Month
-- To Update FiscalYear
UPDATE DimDate
SET FiscalYear =
CASE
WHEN
MONTH(Date)
>= 4 THEN
YEAR(Date)
ELSE
YEAR(Date)
- 1 END
FROM DimDate
-- To Update FiscalMonth
UPDATE DimDate
SET FiscalMonthNumber
=
CASE MonthNumber
WHEN 10 THEN 7
WHEN 11 THEN 8
WHEN 12 THEN 9
WHEN 1 THEN 10
WHEN 2 THEN 11
WHEN 3 THEN 12
WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 3
WHEN 7 THEN 4
WHEN 8 THEN 5
WHEN 9 THEN 6
END
-- To Update FiscalQuarter
UPDATE DimDate
SET FiscalQuarter
=
CASE
WHEN FiscalMonthNumber IN
(1,2,3)
THEN 1
WHEN FiscalMonthNumber IN
(4,5,6)
THEN 2
WHEN FiscalMonthNumber IN
(7,8,9)
THEN 3
WHEN FiscalMonthNumber IN
(10,11,12)
THEN 4
END
-- To Update FiscalQuarterName
UPDATE DimDate
SET FiscalQuarterName =
CAST(FiscalYear AS
VARCHAR(4))
+
' : '
+
'Qtr - '
+
CAST(FiscalQuarter AS
VARCHAR(1))
UPDATE DimDate SET FiscalMonthName =
SUBSTRING(DATENAME(MM,Date),1,3)
+
' - '
+
SUBSTRING(CAST(YEAR(Date)
AS
VARCHAR(4)),3,2)
SELECT
*
FROM DimDate
