大家建时间维度表 时间粒度通常有哪些啊

USE BIWORK_SSIS

GO  

SET NOCOUNT ON 



IF OBJECT_ID('DimDate','U') IS NOT NULL

DROP TABLE DimDate

GO



CREATE TABLE DimDate

(

    DateKey INT PRIMARY KEY,

    FullDate DATE NOT NULL,

    [DateName] NVARCHAR(20),

    DayNumberOfWeek TINYINT NOT NULL,

    DayNameOfWeek NVARCHAR(10) NOT NULL,

    DayNumberOfMonth TINYINT NOT NULL,

    DayNumberOfYear SMALLINT NOT NULL,

    IsWeekend BIT NOT NULL,

    IsLeapYear BIT NOT NULL,

    WeekNumberOfYear TINYINT NOT NULL,

    EnglishMonthName NVARCHAR(10) NOT NULL,

    MonthNumberOfYear TINYINT NOT NULL,

    CalendarQuarter TINYINT NOT NULL,

    CalendarSemester TINYINT NOT NULL,

    CalendarYear SMALLINT NOT NULL, 

    FiscalQuarter TINYINT NOT NULL,

    FiscalSemester TINYINT NOT NULL,

    FiscalYear SMALLINT NOT NULL 

)



DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME



SELECT @StartDate = '2005-01-01',

       @EndDate = '2013-12-31'



WHILE (@StartDate <= @EndDate)

BEGIN

    INSERT INTO DimDate 

    (

        DateKey,

        FullDate,

        [DateName],

        DayNumberOfWeek,

        DayNameOfWeek,

        DayNumberOfMonth,

        DayNumberOfYear,

        IsWeekend,

        IsLeapYear,

        WeekNumberOfYear,

        EnglishMonthName, 

        MonthNumberOfYear,

        CalendarQuarter,

        CalendarSemester,

        CalendarYear, 

        FiscalQuarter,

        FiscalSemester,

        FiscalYear 

    )

    SELECT CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT) AS DateKey,

           CONVERT(VARCHAR(10), @StartDate,20) AS FullDate,

           CONVERT(VARCHAR(20), @StartDate,106) AS [DateName],

           DATEPART(DW,@StartDate) AS DayNumberOfWeek,

           DATENAME(DW,@StartDate) AS DayNameOfWeek,

           DATENAME(DD,@StartDate) AS [DayOfMonth],

           DATENAME(DY,@StartDate) AS [DayOfYear],

           CASE WHEN DATEPART(DW,@StartDate) IN (1,7)  

                    THEN 1

                ELSE 0 

           END AS IsWeekend,

           CASE WHEN ((YEAR(@StartDate) % 4 = 0) AND (YEAR(@StartDate) % 100 != 0 OR YEAR(@StartDate) % 400 = 0))

                    THEN 1

                ELSE 0

           END AS IsLeapYear,

           DATEPART(WW,@StartDate) AS WeekNumberOfYear,

           DATENAME(MM,@StartDate) AS EnglishMonthName,

           DATEPART(MM,@StartDate) AS MonthNumberOfYear,

           DATEPART(QQ,@StartDate) AS CalendarQuarter,

           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6

                    THEN 1

                ELSE 2

           END AS CalendarSemester,

           DATEPART(YY,@StartDate) AS CalendarYear, 

           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6

                     THEN DATEPART(QQ,@StartDate) + 2

                ELSE DATEPART(QQ,@StartDate) - 2

           END AS FiscalQuarter,

           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6

                    THEN 2

                ELSE 1

           END AS FiscalSemester,

           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6

                    THEN DATEPART(YY,@StartDate) 

                ELSE DATEPART(YY,@StartDate) + 1

           END AS FiscalYear

            

    SET @StartDate = @StartDate + 1

END

GO