Website Design United States, Website Design California, Website Designing United States, Website Designing California

Create a Time Dimension Table for a Data Warehouse - SQL

A time dimension is quite common when creating a data warehouse. Here is a quick script for creating a one year time dimension table with granularity to the day.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Time2004' AND type = 'U')
DROP TABLE Time2004

GO

CREATE TABLE Time2004
(
TimeID int IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
theDate SMALLDATETIME,
theDay VARCHAR(10),
theMonth VARCHAR(10),
theYear SMALLINT,
DayOfMonth SMALLINT,
WeekOfYear SMALLINT,
MonthOfYear SMALLINT,
QuarterOfYear VARCHAR(2),
FiscalPeriod SMALLINT
)

GO

DECLARE @CurrDate SMALLDATETIME

SET @CurrDate = '01/01/2004'

WHILE @CurrDate < '01/01/2005'
BEGIN
INSERT Time2004
(theDate, theDay, theMonth, theYear, DayOfMonth,
WeekOfYear, MonthOfYear, QuarterOfYear)
VALUES
(@CurrDate, -- theDate
DATENAME(dw, @CurrDate), -- theDay
DATENAME(month, @CurrDate), -- theMonth
YEAR(@CurrDate), -- theYear
DAY(@CurrDate), -- DayOfMonth
DATENAME(wk, @CurrDate), -- WeekOfYear
MONTH(@CurrDate), -- MonthOfYear
'Q' + DATENAME(quarter,@CurrDate)) -- QuarterOfYear

SET @CurrDate = DATEADD(day, 1, @CurrDate)
END


WEB DESIGN INDIA
42 B Malviya Nagar , New Delhi-110017

Skype: manmeetsi
Email: support.webdesignindia@gmail.com
Tel: 91-011-40502005, 9810067295

 















 


© 2008-2009 dotnet4all.com