關於Sybase資料庫中時間表的建立和生成！

--時間維度表或者時間基礎表

CREATE TABLE DimTime (
TimeKey NUMERIC(18,0) IDENTITY ,
FullDateAlternateKey DATETIME NULL ,
DayNumberOfWeek TINYINT NULL ,
EnglishDayNameOfWeek VARCHAR(10) NULL ,
SpanishDayNameOfWeek VARCHAR(10) NULL ,
FrenchDayNameOfWeek VARCHAR(10) NULL ,
DayNumberOfMonth TINYINT NULL ,
DayNumberOfYear SMALLINT NULL ,
WeekNumberOfYear TINYINT NULL ,
EnglishMonthName VARCHAR(10) NULL ,
SpanishMonthName VARCHAR(10) NULL ,
FrenchMonthName VARCHAR(10) NULL ,
MonthNumberOfYear TINYINT NULL ,
CalENDarQuarter TINYINT NULL ,
CalENDarYear CHAR(4) NULL ,
CalENDarSemester TINYINT NULL ,
FiscalQuarter TINYINT NULL ,
FiscalYear CHAR(4) NULL ,
FiscalSemester TINYINT NULL
)
go

--時間儲存過程
CREATE PROCEDURE p_create_DATETIME
@BeginStr VARCHAR(20) = NULL,
@EndStr VARCHAR(20) = NULL,
@FiscalStartStr VARCHAR(20) = NULL
AS
/*
@BeginStr define the BeginDate created,default will be maxdate FROM dimtime table,format is 'yyyy-mm-dd'
@EndStr define the EndDate created,default will the END date of this year,format is 'yyyy-mm-dd'
@FiscalStartStr define the BeginDate created,default will be '01-01',format is 'mm-dd'
關於財年的計算有點問題，是否1~6月份開始算成當年財年，而7~12月份的算成第二年的財年，也許還需要增加標誌位
*/
DECLARE
@TmpBeginStr VARCHAR(20),
@TmpEndStr VARCHAR(20),
@TmpFiscalStartStr VARCHAR(20),
@BeginDate DATETIME,
@EndDate DATETIME,
@FiscalStartDate DATETIME,
@FiscalBaseDate DATETIME,
@DateDiffer INT

SELECT @TmpBeginStr = @BeginStr
SELECT @TmpEndStr = @EndStr
SELECT @TmpFiscalStartStr = @FiscalStartStr

IF @TmpBeginStr IS NULL
BEGIN
SELECT @BeginDate = DATEADD(dd,1,MAX(FullDateAlternateKey)) FROM DimTime
IF @BeginDate IS NULL
BEGIN
SELECT @TmpBeginStr = CONVERT(VARCHAR(20),getdate(),110)
SELECT @BeginDate = CONVERT(DATETIME,@TmpBeginStr)
END
END
else
BEGIN
SELECT @BeginDate = CONVERT(DATETIME,@TmpBeginStr)
END

IF @TmpEndStr IS NULL
BEGIN
SELECT @TmpEndStr = DATENAME(yy,getdate())+'-12-31'
END
SELECT @EndDate = CONVERT(DATETIME,@TmpEndStr)

IF @TmpFiscalStartStr IS NULL
BEGIN
SELECT @TmpFiscalStartStr = '01-01'
END
SELECT @TmpFiscalStartStr = DATENAME(yy,getdate())+ '-' + @TmpFiscalStartStr

SELECT @FiscalStartDate= CONVERT(DATETIME,@TmpFiscalStartStr)
SELECT @DateDiffer = DATEDIFF(dd,CONVERT(DATETIME,DATENAME(yy,getdate())+'-01-01'),@TmpFiscalStartStr)
SELECT @FiscalBaseDate = DATEADD(dd,-@DateDiffer,@BeginDate)

DELETE FROM DimTime WHERE FullDateAlternateKey >=@BeginDate and FullDateAlternateKey < @EndDate

WHILE @BeginDate < @EndDate
BEGIN
INSERT INTO DimTime
(
--TimeKey , --NUMERIC
FullDateAlternateKey, --DATETIME
DayNumberOfWeek, --TINYINT
EnglishDayNameOfWeek , --VARCHAR
SpanishDayNameOfWeek, --VARCHAR
FrenchDayNameOfWeek , --VARCHAR
DayNumberOfMonth , --TINYINT
DayNumberOfYear , --SMALLINT
WeekNumberOfYear , --TINYINT
EnglishMonthName , --VARCHAR
SpanishMonthName , --VARCHAR
FrenchMonthName , --VARCHAR
MonthNumberOfYear , --TINYINT
CalENDarQuarter , --TINYINT
CalENDarYear , --CHAR
CalENDarSemester , --TINYINT
FiscalQuarter , --TINYINT
FiscalYear , --CHAR
FiscalSemester --TINYINT
)
VALUES
(
--indetity TimeKey ,
@BeginDate,
DATEPART (dw , @BeginDate ) ,
DATENAME (dw , @BeginDate ) ,
'',
'',
DATEPART (dd , @BeginDate ) ,
DATEPART (dy , @BeginDate ) ,
DATEPART (wk , @BeginDate ) ,
DATENAME (mm , @BeginDate ) ,
'',
'',
DATEPART (mm, @BeginDate ) ,
DATEPART (qq , @BeginDate ) ,
CAST(DATEPART (yy , @BeginDate ) AS CHAR(4)),
(DATEPART (mm , @BeginDate )+5)/6 ,
DATEPART (qq , @FiscalBaseDate) ,
CAST(DATEPART (yy , @FiscalBaseDate) AS CHAR(4)),
(DATEPART (mm , @FiscalBaseDate)+5)/6
)
SELECT @BeginDate = dateadd(dd,1,@BeginDate)
SELECT @FiscalBaseDate = dateadd(dd,1,@FiscalBaseDate)
END


--執行方法
exec p_create_DATETIME NULL,NULL , '01-15'
exec p_create_DATETIME '2006-01-01','2006-10-02', '01-15'
SELECT * FROM DimTime