創建DW中時間維度表DimDate
CREATE
TABLE
[
dbo
]
.
[
DimDate
]
(
[
TimeKey
]
int
NOT
NULL
,
[
TheDate
]
datetime
NULL
,
--
DateTime格式的日期
[
TheDateName
]
nvarchar
(
10
)
NULL
,
--
日期名稱
[
TheYear
]
smallint
NULL
,
--
年份
[
TheYearName
]
nvarchar
(
10
)
NULL
,
--
年份名稱
[
TheMonth
]
smallint
NULL
,
--
月份
[
TheMonthName
]
nvarchar
(
10
)
NULL
,
--
月份名稱
[
TheDay
]
smallint
NULL
,
--
日
[
TheDayName
]
nvarchar
(
10
)
NULL
,
--
日的名稱
[
TheQuarter
]
smallint
NULL
,
--
季度
[
TheQuarterName
]
nvarchar
(
10
)
NULL
,
--
季度名稱
[
TheWeek
]
smallint
NULL
,
--
星期
[
TheWeekName
]
nvarchar
(
10
)
NULL
,
--
星期名稱
[
Vacation_Mark
]
smallint
NULL
--
節假日標志
)
ON
[
PRIMARY
]
插入數據
DECLARE
@TheDate
datetime
,
@TheDateName
nvarchar
(
10
),
@TheYear
smallint
,
@TheYearName
nvarchar
(
10
),
@TheMonth
smallint
,
@TheMonthName
nvarchar
(
10
),
@TheDay
smallint
,
@TheDayName
nvarchar
(
10
),
@TheQuarter
smallint
,
@TheQuarterName
nvarchar
(
10
),
@TheWeek
smallint
,
@TheWeekName
nvarchar
(
10
),
@Vacation_Mark
smallint
,
@timeKey
int
,
@dDate
DATETIME
,
--
存儲起始日期和結束日期
@adddays
smallint
--
存儲日期增量
set
@adddays
=
1
--
日期增量
set
@dDate
=
'
1/1/2005
'
--
當前日期
WHILE
@dDate
<=
'
12/31/2020
'
--
結束日期
BEGIN
set
@TheDate
=
@dDate
set
@timeKey
=
cast
((
left
(
convert
(
nvarchar
,
@TheDate
,
23
),
4
)
+
substring
(
convert
(
nvarchar
,
@TheDate
,
23
),
6
,
2
)
+
substring
(
convert
(
nvarchar
,
@TheDate
,
23
),
9
,
2
))
as
int
)
set
@TheDateName
=
REPLACE
(
CONVERT
(
nvarchar
(
20
),
@dDate
,
111
),
'
/
'
,
'
-
'
)
set
@TheYear
=
DATENAME
(yy,
@dDate
)
set
@TheYearName
=
CAST
(
@TheYear
as
nvarchar
)
+
'
年
'
set
@TheMonth
=
DATENAME
(mm,
@dDate
)
set
@TheMonthName
=
CAST
(
@TheMonth
as
nvarchar
)
+
'
月
'
set
@TheDay
=
DATENAME
(dd,
@dDate
)
set
@TheDayName
=
CAST
(
@TheDay
as
nvarchar
)
+
'
日
'
set
@TheQuarter
=
DATENAME
(Quarter,
@dDate
)
set
@TheQuarterName
=
'
第
'
+
CAST
(
DATENAME
(Quarter,
@dDate
)
as
varchar
(
1
))
+
'
季度
'
set
@TheWeek
=
DATEPART
(dw,
@dDate
)
set
@TheWeekName
=
DATENAME
(dw,
@dDate
)
set
@Vacation_Mark
=
CASE
WHEN
(
@TheWeek
=
1
OR
@TheWeek
=
7
)
THEN
1
ELSE
0
END
insert
INTO
DimDate(TimeKey,TheDate,TheDateName,TheYear,TheYearName,TheMonth,TheMonthName,TheDay,
TheDayName,TheQuarter,TheQuarterName,TheWeek,TheWeekName,Vacation_Mark)
VALUES
(
@timeKey
,
@TheDate
,
@TheDateName
,
@TheYear
,
@TheYearName
,
@TheMonth
,
@TheMonthName
,
@TheDay
,
@TheDayName
,
@TheQuarter
,
@TheQuarterName
,
@TheWeek
,
@TheWeekName
,
@Vacation_Mark
)
set
@dDate
=
@dDate
+
@adddays
END
GO
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
