USE [soa]
GO
/****** Object: Table [dbo].[DimDate] Script Date: 07/30/2010 17:18:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimDate](
[DateKey] [int] NOT NULL,
[FullDateAlternateKey] [date] NOT NULL,
[DayNumberOfWeek] [tinyint] NOT NULL,
[ChineseDayNameOfWeek] [nvarchar](10) NOT NULL,
[DayNumberOfMonth] [tinyint] NOT NULL,
[DayNumberOfYear] [smallint] NOT NULL,
[WeekNumberOfYear] [tinyint] NOT NULL,
[EnglishMonthName] [nvarchar](10) NOT NULL,
[ChineseMonthName] [nvarchar](10) NOT NULL,
[MonthNumberOfYear] [tinyint] NOT NULL,
[CalendarQuarter] [tinyint] NOT NULL,
[CalendarYear] [smallint] NOT NULL,
[CalendarSemester] [tinyint] NOT NULL,
CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK_DimDate_FullDateAlternateKey] UNIQUE NONCLUSTERED
(
[FullDateAlternateKey] 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
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期yyyymmdd' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'DateKey'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'带间隔的日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'FullDateAlternateKey'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'一周第几天' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'DayNumberOfWeek'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'星期几' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'ChineseDayNameOfWeek'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'月的每几天' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'DayNumberOfMonth'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年的第几天' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'DayNumberOfYear'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年的第几个星期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'WeekNumberOfYear'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'英文月名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'EnglishMonthName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'中文月名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'ChineseMonthName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数字月' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'MonthNumberOfYear'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'季度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'CalendarQuarter'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimDate', @level2type=N'COLUMN',@level2name=N'CalendarYear'
GO
go
truncate table dimDate
go
--生成一年的时期维护数据,生成从2000年开始到2041年的所有日期维度
declare @curDate datetime
declare @i int
set @curDate='2000-01-01 00:00:00'
set @i=1
WHILE (@i <= 15000)
BEGIN
Insert into dimDate(
DateKey,
FullDateAlternateKey,
DayNumberOfWeek,
ChineseDayNameOfWeek,
DayNumberOfMonth,
DayNumberOfYear,
WeekNumberOfYear,
EnglishMonthName,
ChineseMonthName,
MonthNumberOfYear,
CalendarQuarter,
CalendarYear,
CalendarSemester
)
SELECT
Convert(varchar(8),@curDate,112) as DateKey,
Convert(varchar(10),@curDate,120) as FullDateAlternateKey,
datepart(weekday,@curDate) as DayNumberOfWeek,
(
case datepart(dw,@curDate)
when 1 then '星期天'
when 2 then '星期一'
when 3 then '星期二'
when 4 then '星期三'
when 5 then '星期四'
when 6 then '星期五'
when 7 then '星期六'
end
) as ChineseDayNameOfWeek,
datepart(day,@curDate) as DayNumberOfMonth,
datepart(dayofyear,@curDate) as DayNumberOfYear,
0 as WeekNumberOfYear,--现在是一年中第几个星期,懒了没有写，有时间写这个函数的帮补充一下
(
case datepart(MONTH,@curDate)
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end
) as EnglishMonthName,
(
case datepart(MONTH,@curDate)
when 1 then '一月'
when 2 then '二月'
when 3 then '三月'
when 4 then '四月'
when 5 then '五月'
when 6 then '六月'
when 7 then '七月'
when 8 then '八月'
when 9 then '九月'
when 10 then '十月'
when 11 then '十一月'
when 12 then '十二月'
end
) as ChineseMonthName,
datepart(MONTH,@curDate) as MonthNumberOfYear,
datepart(Quarter,@curDate) as CalendarQuarter,
datepart(year,@curDate) as CalendarYear,
(case when datepart(MONTH,@curDate)>6 then 1 else 2 end) as CalendarSemester
set @i=@i+1
set @curDate=@curDate+1
END
