MS SQL Server 创建时间维度

Simple Steps to create Date Dimension in Microsoft SQL Server.

• 1. Create Two numbers table for sequence/key generation.

CREATE TABLE Numbers_Small (Number INT);
INSERT INTO Numbers_Small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-- Works in SQL Server 2008.Insert statements has to be modified for older versions.

CREATE TABLE Numbers_Big (Number_Big BIGINT);
INSERT INTO Numbers_Big ( Number_Big )
SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number as number_big
FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones;

• 2. Create the Date Dimension table.

CREATE TABLE [dbo].[Date_D](
[DateKey] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[Day] [char](10) NULL,
[DayOfWeek] [smallint] NULL,
[DayOfMonth] [smallint] NULL,
[DayOfYear] [smallint] NULL,
[PreviousDay] [datetime] NULL,
[NextDay] [datetime] NULL,
[WeekOfYear] [smallint] NULL,
[Month] [char](10) NULL,
[MonthOfYear] [smallint] NULL,
[QuarterOfYear] [smallint] NULL,
[Year] [int] NULL
);

• 3. First create DateKey and Date fields.

INSERT INTO Date_D (DateKey, Date)
SELECT number_big, DATEADD(day, number_big,  '2010-01-01') as Date
FROM numbers_big
WHERE DATEADD(day, number_big,  '2010-01-01') BETWEEN '2010-01-01' AND '2010-12-31'
ORDER BY number_big;

Use the following INSERT statement, if you want use a date format(eg: 20100203) as key instead of a number(eg: 123)

INSERT INTO Date_D (DateKey, Date)
SELECT CONVERT(INT, CONVERT(CHAR(10),DATEADD(day, number_big,  '2010-01-01'), 112)) as DateKey,
CONVERT(DATE,DATEADD(day, number_big,  '2010-01-01')) as Date
FROM numbers_big
WHERE DATEADD(day, number_big,  '2010-01-01') BETWEEN '2010-01-01' AND '2010-12-31'
ORDER BY 1;

In above step, the date dimension records will be created for year 2010. Change the dates in the above statement to change the range.

• 4. Update all other fields with appropriate data.

UPDATE Date_D
SET Day = DATENAME(DW, Date),
DayOfWeek = DATEPART(WEEKDAY, Date),
DayOfMonth = DAY(Date),
DayOfYear = DATEPART(DY,Date),
PreviousDay = DATEADD(DAY, -1,  Date),
NextDay = DATEADD(DAY, 1,  Date),
WeekOfYear = DATEPART(WK,Date),
Month = DATENAME(MONTH,Date),
MonthOfYear = MONTH(Date),
QuarterOfYear = DATEPART(Q, Date),
Year = YEAR(Date);

• 5. Drop the temporary number tables.

DROP TABLE Numbers_Small;
DROP TABLE Numbers_Big;