维度表的建立2 时间维度

 

CREATE TABLE [dbo].[DimDate](
    [DateID] [int] NOT NULL primary key,
    [DateName] [datetime] NULL,
    [DateNameCN] [nvarchar](12) NULL,
    [DateFullName] [datetime] NULL,
    [CalendarYear] [int] NULL,
    [CalendarHalfYear] [nvarchar](6) NULL,
    [CalendarQuarter] [nvarchar](6) NULL,
    [CalendarMonth] [int] NULL,
    [CalendarWeek] [int] NULL,
    [DayOfYear] [int] NULL,
    [DayOfHalfYear] [int] NULL,
    [DayOfQuarter] [int] NULL,
    [DayOfMonth] [int] NULL,
    [DayOfWeek] [int] NULL,
    [WeekOfYear] [int] NULL,
    [WeekOfMonth] [int] NULL,
    [WeekName] [nvarchar](9) NULL,
    [WeekNameCN] [nvarchar](9) NULL,
    [WeekScope] [nvarchar](17) NULL,
    [MonthOfYear] [int] NULL,
    [MonthName] [nvarchar](9) NULL,
    [MonthNameCN] [nvarchar](6) NULL,
    [QuarterOfYear] [int] NULL,
    [QuarterName] [nvarchar](2) NULL,
    [QuarterNameCN] [nvarchar](6) NULL,
    [HalfYearOfYear] [int] NULL,
    [HalfYearName] [nvarchar](2) NULL,
    [HalfYearNameCN] [nvarchar](6) NULL,
    [CreateDate] [datetime] NULL default(getdate())
)

 

初始化脚本

--truncate table dimdate
set DATEFIRST 1

declare @dt datetime='2010-01-01'
declare @y nvarchar(4), @m nvarchar(2), @d nvarchar(2), @season nvarchar(1)
while(@dt<'2015-12-31')
begin
set @y=CAST(year(@dt) as nvarchar)
set @m=case when len(CAST(month(@dt) as nvarchar))=1 then '0'+CAST(month(@dt) as nvarchar)
            else CAST(month(@dt) as nvarchar)
       end
set @d=case when len(CAST(day(@dt) as nvarchar))=1 then '0'+CAST(day(@dt) as nvarchar)
            else CAST(day(@dt) as nvarchar)
       end

set @season=
    case when MONTH(@dt)>0 and MONTH(@dt)<=3 then '1'
         when MONTH(@dt)>3 and MONTH(@dt)<=6 then '2'
         when MONTH(@dt)>6 and MONTH(@dt)<=9 then '3'
         when  MONTH(@dt)>9 and MONTH(@dt)<=12 then '4'
    end
insert into DimDate
select
    CONVERT(nvarchar(8),@dt,112) dateid,
    @dt,
    @y+'年'+@m+'月'+@d+'日',
    @dt,
    @y,

    case when MONTH(@dt)>6 then @y+'H2' else @y+'H1' end CalendarHalfYear,
    @y+'Q'+@season,
    @y+@m,
    @y+cast(DATEPART(WEEK,@dt) as nvarchar),
    DATEPART(dayofyear,@dt),

    case when MONTH(@dt)<7 then DATEPART(dayofyear,@dt)
            else DATEPART(dayofyear,@dt)-DATEPART(dayofyear,@y+'-06-30') end DayOfHalfYear,
    case
        when @season='1' then DATEPART(dayofyear,@dt)
        when @season='2' then DATEPART(dayofyear,@dt)-DATEPART(dayofyear,@y+'-03-31')
        when @season='3' then DATEPART(dayofyear,@dt)-DATEPART(dayofyear,@y+'-06-30')
        else DATEPART(dayofyear,@dt)-DATEPART(dayofyear,@y+'-09-30')
        end,
    @d,
    DATEPART(weekday,@dt),
    DATEPART(WEEK,@dt),

    DATEPART(week,@dt)-DATEPART(WEEK,@y+@m+ '01')+1 WeekOfMonth,
--    '',
    case cast(DATEPART(WEEKDAY,@dt) as int) when 1 then 'Monday' when 2 then 'Tuesday' when 3 then 'Wednesday'
         when 4 then 'Thursday' when 5 then 'Friday' when 6 then 'Saturday' when 7 then 'Sunday' end,
    case cast(DATEPART(WEEKDAY,@dt) as int) when 1 then '星期一' when 2 then '星期二' when 3 then '星期三'
         when 4 then '星期四' when 5 then '星期五' when 6 then '星期六' when 7 then '星期日' end,
    CONVERT(nvarchar(8), DATEadd(day,1- DATEPART(weekday,@dt),@dt),112)+'-'+
        CONVERT(nvarchar(8), DATEadd(day,7- DATEPART(weekday,@dt),@dt),112),

    cast(@m as int) MonthOfYear,
    case cast(@m as int) 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 'Octorber' when 11 then 'November' when 12 then 'December' end ,
    case cast(@m as int) 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 ,
    @season,
    'Q'+@season,

    case @season when '1' then '一季度' when '2' then '二季度'
        when '3' then '三季度' when '4' then '四季度' end QuarterNameCN,
    case when MONTH(@dt)>6 then 2 else  1 end,
    case when MONTH(@dt)>6 then 'H2' else  'H1' end,
    case when MONTH(@dt)>6 then '下半年' else  '上半年' end,
    GETDATE()
    ;

set @dt=DATEADD(dd,1,@dt);
end

select * from DimDate

 

if OBJECT_ID('DimTime','U') is null
create table DimTime(
    TimeID int primary key identity(1,1),
    HourID int,
    HourName nvarchar(2),
    MiniteID int,
    Minitename nvarchar(2),
    CreateTime datetime default(getdate())
)
truncate table dimtime
declare @hour int=0;
while(@hour<24)
begin
    declare @mi int=0;
    while(@mi<60)
    begin
        insert into DimTime(HourID, HourName, MiniteID, Minitename)
        select @hour,CAST(@hour as nvarchar), @mi, CAST(@mi as nvarchar)
    set @mi=@mi+1
    end


set @hour=@hour+1
end

select * from DimTime