使用存储过程创建日期维度

CREATE PROCEDURE [dbo].[pBuildDim_Date]
(
    @StartYear int = 1998
    ,@NumYears int = 15
)
AS


DECLARE
    @TmpDate smalldatetime
    ,@EndDate smalldatetime
    ,@LastModDate smalldatetime

SET DATEFIRST 1 -- Week starts on Monday
SET @TmpDate = CONVERT(smalldatetime, ('1/1/' + CONVERT(varchar(4), @StartYear)))
SET @EndDate = DATEADD(yyyy, @NumYears, @TmpDate - 1)
SET @LastModDate = GETDATE()

-- Remove all members.
TRUNCATE TABLE dbo.DimDate

-- Add member to represent unknown or default.
INSERT INTO dbo.DimDate
(
    DateKey
    ,[Date]
    ,YYYYMMDD
    ,[DateName]
    ,[Year]
    ,[Q]
    ,[Qtr]
    ,[Quarter]
    ,[QuarterName]
    ,[MM]
    ,[Mon]
    ,[Month]
    ,[MonthName]
    ,[DD]
    ,[Dy]
    ,[Day]
    ,[DayOfWeek]
    ,[Julian]
    ,[WeekKey]
    ,[Week]
    ,[WeekName]
    ,[Weekend]
    ,YearKey
    ,MoKey
    ,MMMYY
    ,MMMDD
    ,DayActive
    ,HolidayFlag
    ,DTM
)
VALUES
(
    19000101 -- DateKey
    ,'1900-01-01' -- Date
    ,'19000101' -- YYYYMMDD
    ,'01/01/1900' -- DateName
    ,1900 -- Year
    ,0 -- Q
    ,'Q0-00' -- Qtr
    ,'Quarter 0' -- Quarter
    ,'Q0CY00' -- QuarterName
    ,0 -- MM
    ,'NA' -- Mon
    ,'NA' -- Month
    ,'NONE' -- MonthName
    ,0 -- DD
    ,'NA' -- Dy
    ,'NONE' -- Day
    ,0 -- DayOfWeek
    ,0 -- Julian
    ,0 -- WeekKey
    ,0 -- Week
    ,'NONE' -- WeekName
    ,0 -- WeekEnd
    ,-1 -- YearKey
    ,190001 -- MoKey
    ,'NONE' -- MMMYY
    ,'NONE' -- MMMDD
    ,0 -- DayActive
    ,0 -- HolidayFlag (not specified)
    ,@LastModDate -- DTM
)    

-- Add regular date members.
WHILE @TmpDate <= @EndDate
BEGIN
    INSERT INTO dbo.DimDate
    (
        DateKey
        ,[Date]
        ,YYYYMMDD
        ,[DateName]
        ,[Year]
        ,[Q]
        ,[Qtr]
        ,[Quarter]
        ,[QuarterName]
        ,[MM]
        ,[Mon]
        ,[Month]
        ,[MonthName]
        ,[DD]
        ,[Dy]
        ,[Day]
        ,[DayOfWeek]
        ,[Julian]
        ,[WeekKey]
        ,[Week]
        ,[WeekName]
        ,[Weekend]
        ,YearKey
        ,MoKey
        ,MMMYY
        ,MMMDD
        ,DayActive
        ,HolidayFlag
        ,DTM
    )
    VALUES
    (
        CONVERT(int, CONVERT(nchar(8), @TmpDate, 112)) -- DateKey
        ,@TmpDate -- Date
        ,CONVERT(nchar(8), @TmpDate, 112) -- YYYYMMDD
        ,CONVERT(nvarchar(10), @TmpDate, 101) -- DATENAME
        ,DATEPART(yyyy, @TmpDate) -- Year
        ,DATEPART(qq, @TmpDate) -- Q
        ,'Q' + CAST(DATEPART(q, @TmpDate) AS nchar(1)) + '-' + RIGHT(CAST(DATEPART (yy, @TmpDate) AS nchar(4)), 2) -- Qtr
        ,'Quarter ' + CAST(DATEPART(q, @TmpDate) as nchar(1)) -- Quarter
        ,'Q' + CAST(DATEPART(q, @TmpDate) AS nchar(1)) + 'CY' + RIGHT(CAST(DATEPART(yy, @TmpDate) AS nvarchar(4)), 2) -- QuarterName
        ,DATEPART(mm, @TmpDate) -- MM
        ,LEFT(DATENAME(mm, @TmpDate), 3) -- Mon
        ,DATENAME(mm, @TmpDate) -- Month
        ,DATENAME(mm, @TmpDate) + ' ' + CAST(DATEPART(yy, @TmpDate) AS nvarchar(4)) -- MonthName
        ,DATEPART(dd, @TmpDate) -- DD
        ,LEFT(DATENAME(dw, @TmpDate), 3) -- Dy
        ,DATENAME(dw, @TmpDate) -- Day
        ,DATEPART(dw, @TmpDate) -- DayOfWeek
        ,DATEPART(dy, @TmpDate) -- Julian
        ,CONVERT(int, CAST(YEAR(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(4))
            + CASE
                WHEN LEN(MONTH(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101)))) = 1 THEN '0' + CAST(MONTH(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(1))
                ELSE CAST(MONTH(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(2))
                END
            + CASE
                WHEN LEN(DAY(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101)))) = 1 THEN '0' + CAST(DAY(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(1))
                ELSE CAST(DAY(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(2))
                END) -- WeekKey
        ,DATEPART(wk, @TmpDate) -- Week
        ,CAST((CONVERT(nchar(10), (DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))), 101) + ' - '
            +CONVERT(nchar(10), (DATEADD(d, (7 - DATEPART(dw, @TmpDate) ), CONVERT(nvarchar(10), @TmpDate, 101))), 101)) AS nvarchar(25)) -- WeekName
        ,CASE                              
            WHEN DATEPART(dw, @TmpDate) in (6, 7) THEN 1
            ELSE 0
            END -- Weekend
        ,@NumYears - (DATEPART(yyyy, @TmpDate) - @StartYear) -- YearKey
        ,DATEPART(yyyy, @TmpDate) * 100 + DATEPART(mm, @TmpDate) -- MoKey
        ,LEFT(DATENAME(mm, @TmpDate),3) + '-' + RIGHT(CAST(DATEPART(yy, @TmpDate) AS nchar(4)), 2) -- MMMYY
        ,LEFT(DATENAME(mm, @TmpDate),3) + '-' + CAST(DATEPART(dd, @TmpDate) AS nchar(2)) -- MMMDD
        ,CASE                                                      
            WHEN DATEDIFF(m, @TmpDate, @EndDate) > 25 THEN 0
            ELSE 1
            END -- DayActive
        ,0 -- HolidayFlag (not specified)
        ,@LastModDate -- DTM
    )

    SET @TmpDate = DATEADD(dd, 1, @TmpDate)
END  

SET QUOTED_IDENTIFIER OFF