Tuesday, February 07, 2012

SQl server 2008 script to create and populate a date dimension table


This script was based and improved upon an idea (with some bugs) on sqlservercentral.com. I put it here so that I can easily reuse it.

CREATE TABLE dbo.DateDim(
DateID int IDENTITY(1,1) NOT NULL,

ActualDate date NOT NULL,

[Year] smallint NOT NULL,

[Quarter] smallint NOT NULL,

[Month] smallint NOT NULL,

[Week] smallint NOT NULL,

[DayofYear] smallint NOT NULL,

[DayofMonth] smallint NOT NULL,

[DayofWeek] smallint NOT NULL,

IsWeekend bit NOT NULL,

IsHoliday bit NOT NULL,

CalendarWeek smallint NOT NULL,

BusinessYearWeek smallint NOT NULL,

LeapYear bit NOT NULL,
CONSTRAINT PK_DateDim PRIMARY KEY CLUSTERED
(

DateID 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
 ALTER TABLE dbo.DateDim WITH CHECK ADD CONSTRAINT FK_DateDim_DateDim FOREIGN KEY(DateID)

REFERENCES dbo.DateDim (DateID)

GO



ALTER TABLE dbo.DateDim CHECK CONSTRAINT FK_DateDim_DateDim

GO
 CREATE PROCEDURE dbo.sp_createTimeDim AS


--declare variables
DECLARE @DT DATE
DECLARE @YEAR smallint
DECLARE @QUARTER smallint
DECLARE @MONTH smallint
DECLARE @WEEK smallint
DECLARE @DayofYear smallint
DECLARE @DayofMonth smallint
DECLARE @DayofWeek smallint
DECLARE @IsWeekend BIT
DECLARE @IsHoliday BIT
DECLARE @CalendarWeek smallint
DECLARE @BusinessYearWeek INT
DECLARE @LeapYear BIT

--initialize variables


SELECT @BusinessYearWeek =0
SELECT @CalendarWeek = 1
SELECT @LeapYear =0

--the starting date for the date dimension
SELECT @DT = '2012-01-01'

--start looping, stop at ending date

WHILE (@DT <= '2020-12-31')


BEGIN
--get information about the data

SET @IsWeekend =0
SET @YEAR = DATEPART (YEAR, @DT)
SET @QUARTER = DATEPART (QUARTER, @DT)
SET @MONTH = DATEPART (MONTH , @DT)
SET @WEEK = DATEPART (WEEK , @DT)
SET @DayofYear = DATEPART (DY , @DT)
SET @DayofMonth = DATEPART (DAY , @DT)
SET @DayofWeek = DATEPART (DW , @DT)


--note if weeknd or not


IF ( @DayofWeek = 1 OR @DayofWeek = 7 )
BEGIN
SELECT @IsWeekend = 1
END


--add 1 every time we start a new week


IF ( @DayofWeek = 1)

BEGIN
SELECT @CalendarWeek = @CalendarWeek +1

END


--add business rule (need to know complete weeks in a year, so a partial week in new year set to 0)


IF ( @DayofWeek != 1 AND @DayofYear = 1)

BEGIN

SELECT @BusinessYearWeek = 0

END


IF ( @DayofWeek = 1)

BEGIN

SELECT @BusinessYearWeek = @BusinessYearWeek +1

END

--add business rule (start counting business weeks with first complete week)

IF (@BusinessYearWeek =53)

BEGIN

SELECT @BusinessYearWeek = 1

END

--check for leap year


IF ((@YEAR % 4 = 0) AND (@YEAR % 100 != 0 OR @YEAR % 400 = 0))

SELECT @LeapYear =1

ELSE SELECT @LeapYear =0

--insert values into Date Dimension table

INSERT Mgmt.DateDim (ActualDate, Year, Quarter, Month, Week, DayofYear, DayofMonth, DayofWeek, IsWeekend, CalendarWeek, BusinessYearWeek, LeapYear, IsHoliday)

VALUES (@DT, @YEAR, @QUARTER, @MONTH, @WEEK, @DayofYear, @DayofMonth, @DayofWeek, @IsWeekend, @CalendarWeek, @BusinessYearWeek, @LeapYear, @IsHoliday)

--increment the date one day


SELECT @DT = DATEADD(DAY, 1, @DT)

END
GO


DECLARE @RC int


EXECUTE @RC = dbo.sp_createTimeDim


GO




4 comments:

ninest123 said...

louis vuitton outlet, sac longchamp, ralph lauren pas cher, replica watches, louboutin outlet, louboutin shoes, christian louboutin outlet, prada outlet, ugg boots, longchamp pas cher, tiffany and co, louis vuitton, louboutin, air jordan pas cher, tory burch outlet, cheap oakley sunglasses, nike outlet, polo ralph lauren outlet, ray ban sunglasses, polo ralph lauren outlet, louis vuitton outlet, nike air max, replica watches, louis vuitton, air max, longchamp outlet, michael kors, oakley sunglasses, chanel handbags, nike free, nike roshe run, oakley sunglasses, burberry, tiffany jewelry, ray ban sunglasses, kate spade outlet, prada handbags, oakley sunglasses, nike air max, louis vuitton, louboutin pas cher, uggs on sale, ray ban sunglasses, oakley sunglasses, longchamp, jordan shoes, gucci outlet, ugg boots, nike free, longchamp outlet

ninest123 said...

vanessa bruno, coach outlet, nike free run uk, michael kors, michael kors outlet, coach outlet, replica handbags, new balance pas cher, michael kors outlet, hollister pas cher, michael kors, converse pas cher, nike air max, tn pas cher, ray ban pas cher, michael kors, abercrombie and fitch, lululemon, ralph lauren uk, vans pas cher, michael kors, michael kors outlet, mulberry, nike air max, kate spade handbags, nike air max, hermes, ugg boots, hollister, nike roshe, lacoste pas cher, oakley pas cher, nike blazer, ugg boots, true religion jeans, michael kors outlet, sac guess, air force, true religion outlet, hogan, ray ban uk, coach purses, timberland, north face, burberry, michael kors outlet, true religion jeans, burberry outlet online, north face, true religion jeans

ninest123 said...

lancel, celine handbags, jimmy choo shoes, bottega veneta, nike roshe, asics running shoes, gucci, giuseppe zanotti, nike huarache, mcm handbags, herve leger, hollister, hollister, new balance, ray ban, p90x workout, soccer shoes, babyliss, longchamp, mac cosmetics, nike trainers, chi flat iron, mont blanc, vans, vans shoes, ghd, iphone cases, nike air max, hollister, ferragamo shoes, ralph lauren, louboutin, nike air max, beats by dre, valentino shoes, converse outlet, lululemon, north face outlet, instyler, soccer jerseys, birkin bag, insanity workout, baseball bats, north face outlet, abercrombie and fitch, timberland boots, reebok shoes, nfl jerseys, oakley, wedding dresses

ninest123 said...

hollister, louis vuitton, canada goose outlet, moncler, pandora jewelry, swarovski, louis vuitton, bottes ugg, coach outlet, moncler, supra shoes, montre pas cher, moncler, ugg,uggs,uggs canada, ugg,ugg australia,ugg italia, canada goose, links of london, pandora jewelry, karen millen, doudoune canada goose, juicy couture outlet, moncler, pandora charms, marc jacobs, swarovski crystal, moncler, ugg pas cher, thomas sabo, louis vuitton, moncler outlet, moncler, canada goose, canada goose uk, canada goose outlet, ugg boots uk, juicy couture outlet, wedding dresses, moncler, canada goose, toms shoes, louis vuitton, replica watches, sac louis vuitton pas cher, pandora charms, canada goose