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