USE [DatabaseName]
GO
IF OBJECT_ID('Date','U') IS NOT NULL
DROP TABLE Date
GO
CREATE TABLE [dbo].[Date](
[DateSK] [int] NOT NULL,
[FullDate] [datetime] NOT NULL,
[DateName] [char](11) NOT NULL,
[DayOfWeek] [tinyint] NOT NULL,
[DayNameOfWeek] [char](10) NOT NULL,
[DayOfMonth] [tinyint] NOT NULL,
[DayOfYear] [smallint] NOT NULL,
[WeekdayWeekend] [char](7) NOT NULL,
[WeekOfYear] [tinyint] NOT NULL,
[MonthName] [char](10) NOT NULL,
[MonthOfYear] [tinyint] NOT NULL,
[CalendarQuarter] [tinyint] NOT NULL,
[CalendarYear] [smallint] NOT NULL,
[CalendarYearMonth] [char](7) NOT NULL,
[CalendarYearQtr] [char](15) NOT NULL,
CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)
) ON [PRIMARY]
GO
RAISERROR('Table Date created successfully!',0,1)
DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'
WHILE (@StartDate <= @EndDate )
BEGIN
INSERT INTO Date
SELECT
CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK
,@StartDate AS [Date]
,CONVERT(varchar(20),@StartDate,106) AS DateName
,DATEPART(DW,@StartDate) [DayOfWeek]
,DATENAME(DW,@StartDate) [DayNameOfWeek]
,DATENAME(DD,@StartDate) [DayOfMonth]
,DATENAME(DY,@StartDate) [DayOfYear]
,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd'
ELSE 'WeekDay' END [WeekdayWeekend]
,DATEPART(WW,@StartDate) [WeekOfYear]
,DATENAME(MM ,@StartDate) [MonthName]
,DATEPART(MM ,@StartDate) [MonthOfYear]
,DATEPART(QQ,@StartDate) [CalendarQuarter]
,DATEPART(YY ,@StartDate) [CalendarYear]
,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2) [CalendarYearMonth]
,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr]
SET @StartDate = @StartDate +1
END
GO
Date Dimension is ready to use as soon as you execute this script in required database.
You can create following Calendar Hierarchy in your dimension to provide drill down featrues for Growth, Trends, and ToDate calculations (e.g. YTD, QTD, MTD).
Year-->Quarter-->Month-->Week-->Date
No comments:
Post a Comment