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

最新推荐文章于 2022-06-13 23:58:08 发布

原创最新推荐文章于 2022-06-13 23:58:08 发布 · 621 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #date #insert #table #qq

数据仓库与商业智能专栏收录该内容

42 篇文章

订阅专栏

本文介绍了一个用于构建日期维度表的存储过程，该过程能够为指定的年份范围生成详细的日期层级数据，包括日期、星期、季度等信息，适用于数据仓库中的时间维度构建。

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