关于Sybase数据库中时间表的创建和生成！

--时间维度表或者时间基础表

CREATE TABLE DimTime (ITPUB个人空间TE9EK,N'x
B.N
TimeKey NUMERIC(18,0) IDENTITY ,ITPUB个人空间y@m:S(kG3Zy?o9F
FullDateAlternateKey DATETIME NULL ,
AipQ8C.{ z0DayNumberOfWeek TINYINT NULL ,
ruwh|o+qAfEn0EnglishDayNameOfWeek VARCHAR(10) NULL ,
]3ol D5W3tG c|0SpanishDayNameOfWeek VARCHAR(10) NULL ,ITPUB个人空间%@?RX c
[ He3_M
FrenchDayNameOfWeek VARCHAR(10) NULL ,ITPUB个人空间Sj%Bt(R"s"O
DayNumberOfMonth TINYINT NULL ,ITPUB个人空间C"X\%ML$T.r5U
DayNumberOfYear SMALLINT NULL ,ITPUB个人空间"uxDH0]
WeekNumberOfYear TINYINT NULL ,ITPUB个人空间4]m3xn\nt
EnglishMonthName VARCHAR(10) NULL ,ITPUB个人空间gPg6n,a g
SpanishMonthName VARCHAR(10) NULL ,
tyY.b V|J
D0FrenchMonthName VARCHAR(10) NULL ,ITPUB个人空间T)D^1D`nI M
MonthNumberOfYear TINYINT NULL ,ITPUB个人空间7Ls7w~7s7y
CalENDarQuarter TINYINT NULL ,ITPUB个人空间|g)w6UmB-_^!`*sg8m
CalENDarYear CHAR(4) NULL ,ITPUB个人空间j2yUtm
CalENDarSemester TINYINT NULL ,ITPUB个人空间\!xuG8L$]8T
FiscalQuarter TINYINT NULL ,ITPUB个人空间j9\'s1~6?2Z b1w
FiscalYear CHAR(4) NULL ,ITPUB个人空间&a-s_6v6mgwZYn P\
FiscalSemester TINYINT NULL
ok*} b{FKa u0)
7t)aN6u)}-L!LjD0goITPUB个人空间/y["j.Js2g1j

[c f%`!V{a6h@0--时间存储过程ITPUB个人空间mL?B7~J ^C3W,cV
CREATE PROCEDURE p_create_DATETIME
2~ZV/VLZ^0@BeginStr VARCHAR(20) = NULL,
K[Z(I;A~$Q0@EndStr VARCHAR(20) = NULL,ITPUB个人空间B Noq;w7k~9G
@FiscalStartStr VARCHAR(20) = NULLITPUB个人空间%u`VPG p*P['D2T
AS
,B-d"^2xJ"k0/*ITPUB个人空间oY'^HU&{ [ ^t!o
@BeginStr define the BeginDate created,default will be maxdate FROM dimtime table,format is 'yyyy-mm-dd'
D/D(pSp \-w
hw&L0@EndStr define the EndDate created,default will the END date of this year,format is 'yyyy-mm-dd'ITPUB个人空间hYQ n V#Y$l2Ai
@FiscalStartStr define the BeginDate created,default will be '01-01',format is 'mm-dd'ITPUB个人空间%e8IXq&WS
关于财年的计算有点问题，是否1~6月份开始算成当年财年，而7~12月份的算成第二年的财年，也许还需要增加标志位
p_ Kz&A#Lf$J0*/ITPUB个人空间YqJ"HY;\.?!k9s
DECLARE
'F R.Yu;HY}l-J0@TmpBeginStr VARCHAR(20),
|6?Ll9J {M(~0@TmpEndStr VARCHAR(20),ITPUB个人空间%h"c@}&uQ
@TmpFiscalStartStr VARCHAR(20),
M3{ `/` tu(z0@BeginDate DATETIME,
1fiZ%y2t*o0@EndDate DATETIME,
6fO\dn0@FiscalStartDate DATETIME,
$Y `6w;AF/PVR4yG(}0@FiscalBaseDate DATETIME,ITPUB个人空间(_0_2Zqau{rB|6Z#u
@DateDiffer INTITPUB个人空间4wdL3Nr(`
ITPUB个人空间&x V @X
o
SELECT @TmpBeginStr = @BeginStrITPUB个人空间i'L$xb
Jfn
SELECT @TmpEndStr = @EndStr
F8fz'F XR0KZ0SELECT @TmpFiscalStartStr = @FiscalStartStr
:n/jm{O0
^3jb0r,Q(D
|0IF @TmpBeginStr IS NULL
O gr n'v|%M0BEGIN
#R2k'T$kMN#|loO&o0SELECT @BeginDate = DATEADD(dd,1,MAX(FullDateAlternateKey)) FROM DimTime
yN}1a%Czrf%z x(U0IF @BeginDate IS NULL
N#NZ,V L/Ae&?0BEGINITPUB个人空间7S'Uzc8neQk
SELECT @TmpBeginStr = CONVERT(VARCHAR(20),getdate(),110)ITPUB个人空间q2v.XBwE$uI]
SELECT @BeginDate = CONVERT(DATETIME,@TmpBeginStr)ITPUB个人空间d!et%M!T
ENDITPUB个人空间QYv8u
|5u&l-E&]$|_
ENDITPUB个人空间*[fu of,h(F
Tf)l
else
&Bk]^q6b8N8R+j0B0BEGIN
2Ow [8W5L*N|0SELECT @BeginDate = CONVERT(DATETIME,@TmpBeginStr)
N'Ao)}B*e0ENDITPUB个人空间DZo9I;C8O u h
ITPUB个人空间5s0O(O ?dIK&X
IF @TmpEndStr IS NULLITPUB个人空间]^/_ ?+Ax2P|5G
BEGIN
f_/Ej-G,K^0SELECT @TmpEndStr = DATENAME(yy,getdate())+'-12-31'ITPUB个人空间{W1A1[ Lm'r
END

JT mi
a)PS0SELECT @EndDate = CONVERT(DATETIME,@TmpEndStr)ITPUB个人空间N0j@DS/x)y
ITPUB个人空间YAK,X B4cU$OAv)e
IF @TmpFiscalStartStr IS NULLITPUB个人空间 ~l^`$Lm^L)_
BEGIN
[4v$uD!^op0SELECT @TmpFiscalStartStr = '01-01'
J Mt"OF*~r|sF:x0END
7{Q]R8s0SELECT @TmpFiscalStartStr = DATENAME(yy,getdate())+ '-' + @TmpFiscalStartStrITPUB个人空间W(wz0h{\~#a\H+X
ITPUB个人空间jq*o5AQ,hr2Jg
SELECT @FiscalStartDate= CONVERT(DATETIME,@TmpFiscalStartStr)
7\a^m8AWYe0SELECT @DateDiffer = DATEDIFF(dd,CONVERT(DATETIME,DATENAME(yy,getdate())+'-01-01'),@TmpFiscalStartStr)ITPUB个人空间gPI^4P
SELECT @FiscalBaseDate = DATEADD(dd,-@DateDiffer,@BeginDate)ITPUB个人空间S&a`Z i.ss"J3R4L
ITPUB个人空间y%h
BF:q5Cp;}:T
DELETE FROM DimTime WHERE FullDateAlternateKey >=@BeginDate and FullDateAlternateKey < @EndDateITPUB个人空间MI!K8O2d*pM+W}1^

H? ZF1J0WHILE @BeginDate < @EndDate
7N&Z^Q2OJ0iM^x0BEGINITPUB个人空间-V*c.mlb
INSERT INTO DimTimeITPUB个人空间QL#p7A\9|x$Ve
(
bE+F~8E0--TimeKey , --NUMERICITPUB个人空间vy{WK#a,E(n
FullDateAlternateKey, --DATETIME
{
J?e-P`{9C0DayNumberOfWeek, --TINYINTITPUB个人空间d"ay,Z3^XZ5}6Jw*|
EnglishDayNameOfWeek , --VARCHARITPUB个人空间X5mp8v+F9`&s3]
SpanishDayNameOfWeek, --VARCHAR
+n9R'XZP y$bxb0FrenchDayNameOfWeek , --VARCHAR
-QF5b9N;QzZ0DayNumberOfMonth , --TINYINTITPUB个人空间[9S#[.L PVy
DayNumberOfYear , --SMALLINT
o `:yt.U7lZ!h/b0WeekNumberOfYear , --TINYINT
5r"C Y
iww M(f6fyO0EnglishMonthName , --VARCHARITPUB个人空间o[q4T [']_}
SpanishMonthName , --VARCHARITPUB个人空间'gnZ+z(gt
FrenchMonthName , --VARCHARITPUB个人空间J^ \!tS
MonthNumberOfYear , --TINYINTITPUB个人空间vD`pRq
CalENDarQuarter , --TINYINTITPUB个人空间~ E2d KW pLw3w
CalENDarYear , --CHAR
2]3rq5c-L/}`2]:RL^oQ0CalENDarSemester , --TINYINT
*c#~zf$Ou0FiscalQuarter , --TINYINT
T.B:yC F W x|3[{0FiscalYear , --CHARITPUB个人空间!n r-D3`)rSja}
FiscalSemester --TINYINTITPUB个人空间:Y,_'PM(g:\.e
)
,J$hE,j4P2A0VALUES
UPx ^tBfdK_ |0(
!l]%hDu#E A:Ur0--indetity TimeKey ,
5eth V/zBR0@BeginDate,ITPUB个人空间qP*k5CqX
DATEPART (dw , @BeginDate ) ,ITPUB个人空间M!m\&AewS
DATENAME (dw , @BeginDate ) ,
po7|0t(H:d _0'',
q1G:f;GmC@0e0'',ITPUB个人空间7]] \Z,z|w,N+V
DATEPART (dd , @BeginDate ) ,ITPUB个人空间!]P"{q/ox
DATEPART (dy , @BeginDate ) ,
4_"VQh
]vx0DATEPART (wk , @BeginDate ) ,
^&?lZ:I;@ \`0DATENAME (mm , @BeginDate ) ,ITPUB个人空间)T g;tY]{1cIt
'',
%ZFm8c,o!yJF0'',ITPUB个人空间 Z&p"hd"[ lw6[&ik@
DATEPART (mm, @BeginDate ) ,ITPUB个人空间is }*P\(BT6J
DATEPART (qq , @BeginDate ) ,
em"H vZN]gZ0CAST(DATEPART (yy , @BeginDate ) AS CHAR(4)),
M ?(O?Syr4Q0(DATEPART (mm , @BeginDate )+5)/6 ,ITPUB个人空间Rn!hY J
DATEPART (qq , @FiscalBaseDate) ,
k)`/t C5m'l0G0CAST(DATEPART (yy , @FiscalBaseDate) AS CHAR(4)),
,J9} i%qF#Le4H0(DATEPART (mm , @FiscalBaseDate)+5)/6ITPUB个人空间2g/Qoq6sk&TJ2i1r{
)ITPUB个人空间(vJO,?'f5J2}5^
SELECT @BeginDate = dateadd(dd,1,@BeginDate)
]D7|9T!t6T&_k0SELECT @FiscalBaseDate = dateadd(dd,1,@FiscalBaseDate)
g$j"K k6q)L0END
?k@&U@['N1]BC0ITPUB个人空间)G)h.|5ks8zUYN
ITPUB个人空间 i6sDYD
y
--执行方法ITPUB个人空间N4M2[t5YUr EX
exec p_create_DATETIME NULL,NULL , '01-15'
C`)jXs0heq1~-}N0g0exec p_create_DATETIME '2006-01-01','2006-10-02', '01-15'
mXd3j7HS3s0SELECT * FROM DimTime