SQL生成日期维度(到小时)

#建表语句:

CREATE TABLE [dbo].[Dim_日期3](
    [日期3ID] [varchar](10) NOT NULL,
    [] [int] NULL,
    [半年] [varchar](6) NULL,
    [] [varchar](2) NULL,
    [] [varchar](4) NULL,
    [] [varchar](6) NULL,
    [星期] [varchar](6) NULL,
    [是否周末] [varchar](4) NULL,
    [] [int] NULL,
    [] [int] NULL,
 CONSTRAINT [PK_Dim_日期3] PRIMARY KEY CLUSTERED 
(
    [日期3ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

#生成数据:

DECLARE @BeginDate DATE;

SELECT @BeginDate = 2013-1-1;

WHILE @BeginDate <= 2014-12-31
  BEGIN
      DECLARE @hour INT
      DECLARE @hourChar VARCHAR(2)

      SET @hour=0
      SET @BeginDate = Dateadd(DAY, 1, @BeginDate);

      WHILE @hour < 24
        BEGIN
            SET @hourChar=RIGHT(CONVERT(VARCHAR(3), 100+@hour), 2)

            INSERT INTO [Dim_日期3]
            SELECT CONVERT(VARCHAR(8), @BeginDate, 112) + @hourChar AS [日期ID],
                   Year(@BeginDate)                                 AS [],
                   CASE
                     WHEN Datepart(month, @BeginDate) <= 6 THEN 上半年
                     ELSE 下半年
                   END                                              AS [半年],
                   CASE
                     WHEN Datename (qq, @BeginDate) = 1 THEN Q1
                     WHEN Datename (qq, @BeginDate) = 2 THEN Q2
                     WHEN Datename (qq, @BeginDate) = 3 THEN Q3
                     ELSE Q4
                   END                                              AS [],
                   CASE
                     WHEN Month(@BeginDate) = 1 THEN 01月
                     WHEN Month(@BeginDate) = 2 THEN 02月
                     WHEN Month(@BeginDate) = 3 THEN 03月
                     WHEN Month(@BeginDate) = 4 THEN 04月
                     WHEN Month(@BeginDate) = 5 THEN 05月
                     WHEN Month(@BeginDate) = 6 THEN 06月
                     WHEN Month(@BeginDate) = 7 THEN 07月
                     WHEN Month(@BeginDate) = 8 THEN 08月
                     WHEN Month(@BeginDate) = 9 THEN 09月
                     WHEN Month(@BeginDate) = 10 THEN 10月
                     WHEN Month(@BeginDate) = 11 THEN 11月
                     ELSE 12月
                   END                                              AS [],
                   CASE
                     WHEN Datepart(WEEK, @BeginDate) < 10 THEN 第0 + CONVERT(VARCHAR(2), Datepart(WEEK, @BeginDate)) + 
                     ELSE  + CONVERT(VARCHAR(2), Datepart(WEEK, @BeginDate)) + 
                   END                                              AS [],
                   Datename(WeekDay, @BeginDate)                    AS [星期],
                   CASE
                     WHEN Datepart(dw, @BeginDate) = 6 THEN 周末
                     WHEN Datepart(dw, @BeginDate) = 7 THEN 周末
                     ELSE 平时
                   END                                              AS 是否周末,
                   Day(@BeginDate)                                  AS [],
                   @hour                                            AS []

            SET @hour=@hour + 1
        END
  END; 

 

SQL生成日期维度(到小时),古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。