http://www.sqlservercentral.com/blogs/dknight/2010/11/08/date-dimension-using-computed-columns/

Printed 2014/07/29 01:14PM

Date Dimension Using Computed Columns

2010/11/08

Picked up this nice script from a client last week.  Use this script below to create a Date Dimension for a data warehouse that is based off of computed columns. The only column that needs to be populated is the CalendarDate column.


CREATE
TABLE [DimDate](
[DateKey] [int] IDENTITY(1,1)NOT
NULL,
[CalendarDate] [datetime]
NULL,
[CalendarYearNumber] AS (datepart(year,[CalendarDate]
)),
[CalendarYearName] AS (CONVERT([varchar](4),datepart(year,[CalendarDate]),(0)))PERSISTED
,
[CalendarQuarterNumber] AS (datepart(quarter,[CalendarDate]
)),
[CalendarQuarterName] AS ('QTR'+CONVERT([varchar](1),datepart(quarter,[CalendarDate]),(0
))),
[CalendarMonthNumber] AS (datepart(month,[CalendarDate]
)),
[CalendarMonthName] AS (datename(month,[CalendarDate]
)),
[CalendarMonthNameShort] AS (left(datename(month,[CalendarDate]),(3
))),
[DayNumberOfWeek] AS (datepart(weekday,[CalendarDate]
)),
[DayNameOfWeek] AS (datename(weekday,[CalendarDate]
)),
[CalendarDayNumberOfMonth] AS (datepart(day,[CalendarDate]
)),
CONSTRAINT [PKdimDate] PRIMARYKEYCLUSTERED
(
[DateKey]
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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.