创建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
