Date Dimension Create

간단한 일자 차원생성 

CREATE TABLE CALENDAR

(

  DATE  CHAR(8)

, YEAR  CHAR(4)

, MONTH  CHAR(2)

, DAY   CHAR(2)

, HALF  CHAR(1)

, QUARTER  CHAR(1)

, WEEKDAY  CHAR(6)

, WEEK  CHAR(2)

, DAY_OF_YEAR CHAR(3)

)

---------------------------------------------------------------

DECLARE @START_DATE CHAR(8)

,  @END_DATE CHAR(8)

SELECT @START_DATE = '20070101'

,  @END_DATE = '20081231'

WHILE @START_DATE <= @END_DATE

BEGIN

INSERT CALENDAR

VALUES (

 @START_DATE

, LEFT(@START_DATE,4)

, SUBSTRING(@START_DATE,5,2)

, RIGHT(@START_DATE,2)

, (CASE WHEN SUBSTRING(@START_DATE,5,2)<= 6 THEN 1 ELSE 2 END)

, DATENAME (QQ, @START_DATE)

, DATENAME (DW, @START_DATE)

, DATENAME (WW, @START_DATE)

, DATENAME (DY, @START_DATE)

)

SET @START_DATE = CONVERT(CHAR(8),DATEADD(D,1,@START_DATE),112)

END