原文:
SQL點(diǎn)滴5—產(chǎn)生時(shí)間demention，主要是時(shí)間轉(zhuǎn)換
數(shù)據(jù)倉(cāng)庫(kù)中有時(shí)間表，存儲(chǔ)時(shí)間信息，這個(gè)存儲(chǔ)過(guò)程接收開(kāi)始時(shí)間結(jié)束時(shí)間，寫(xiě)入時(shí)間具體信息。有高手用excel函數(shù)功能很快能產(chǎn)生INSERT語(yǔ)句不會(huì)啊，只能用這個(gè)。
1
vSET ANSI_NULLS
ON
2
?
GO
3
4
?
SET
QUOTED_IDENTIFIER
ON
5
GO
6
7
/*
--------------------------------------------------------
8
*generate Date demention
9
*@startdate '01/01/2010'
10
*@enddate '12/30/2010'
11
---------------------------------------------------------
*/
12
13
CREATE
PROCEDURE
dbo.AddDateTime
14
@startdate
datetime
,
15
@enddate
datetime
16
AS
17
declare
@i
int
18
set
@i
=
1
19
20
while
(
DATEDIFF
(DD,
@startdate
,
@enddate
)
>=
0
)
21
begin
22
23
insert
into
[
ReportServer
]
.
[
dbo
]
.
[
D_DATE
]
(
24
[
DATE_KEY
]
,
--
primarykey
25
[
DATE
]
,
--
datetime
26
[
FULL_DATE_DESCRIPTION
]
,
--
detail date
27
[
DAY_OF_WEEK
]
,
--
day of week from 1 to 6
28
[
CALENDAR_MONTH
]
,
--
month of year from 1 to 12
29
[
CALENDAR_YEAR
]
,
--
year
30
[
FISCAL_YEAR_MONTH
]
,
--
fiscal year
31
[
HOLIDAY_INDICATOR
]
,
--
holiday iden
32
[
WEEKDAY_INDICATOR
]
)
33
select
34
@i
,
35
CONVERT
(
varchar
(
10
),
@startdate
,
101
),
36
DATENAME
(mm,
@startdate
)
+
'
'
+
DATENAME
(DD,
@startdate
)
+
'
,
'
+
DATENAME
(YYYY,
@startdate
),
37
DATEPART
(DW,
@startdate
),
38
DATEPART
(MM,
@startdate
),
39
DATEPART
(YYYY,
@startdate
),
40
'
F
'
+
CONVERT
(
varchar
(
7
),
@startdate
,
23
),
41
case
42
when
(
CONVERT
(
varchar
(
5
),
@startdate
,
101
)
in
(
'
01/01
'
,
'
01/05
'
,
'
02/05
'
,
'
03/05
'
,
'
01/10
'
,
'
02/10
'
,
'
03/10
'
,
'
04/10
'
,
'
05/10
'
,
'
06/10
'
) )
then
1
43
else
0
end
,
44
case
45
when
(
DATENAME
(DW,
@startdate
)
in
(
'
Saturday
'
,
'
Sunday
'
))
then
1
46
else
0
end
47
48
if
DATEDIFF
(DD,
@startdate
,
@enddate
)
>=
0
49
50
begin
51
set
@startdate
=
DATEADD
(dd,
1
,
@startdate
)
52
set
@i
=
@i
+
1
53
continue
54
end
55
56
else
57
58
begin
59
break
60
end
61
62
end
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
