midrange.com code scratchpad
Name:
DATE_DIM
Scriptlanguage:
SQL
Tabwidth:
4
Date:
01/04/2016 08:24:38 pm
IP:
Logged
Description:
DATE_DIM is a table that contains every date within what would be considered reasonable time period for a company.
Normally there is no need to have more than 200 years of dates for any processing.

Each record in the table is a specific date. The extra columns on that record allow for joining the date data to multiple different formats.
They also make it easier to group SQL result sets by an attribute of date.

The code here is included for reference only. It will load *most* of the columns of the represented DATE_DIM table.
Some of the columns, like fiscal year, are more specific to a business and would therefore not be very good for a generic load script.
Code:
  1. CREATE TABLE DATE_DIM (
  2.   D_DATE_SK INTEGER NOT NULL
  3.  ,D_DATE DATE DEFAULT NULL
  4.  ,D_MONTH_SEQ FOR COLUMN D_MSEQ INTEGER DEFAULT NULL
  5.  ,D_WEEK_SEQ FOR COLUMN D_WSEQ INTEGER DEFAULT NULL
  6.  ,D_QUARTER_SEQ FOR COLUMN D_QSEQ INTEGER DEFAULT NULL
  7.  ,D_PERIOD_SEQ FOR COLUMN D_PSEQ INTEGER DEFAULT NULL
  8.  ,D_YEAR INTEGER DEFAULT NULL
  9.  ,D_DOW INTEGER DEFAULT NULL
  10.  ,D_DAYOFWEEKINMONTH For Column D_DOWINM Integer Default Null
  11.  ,D_MOY INTEGER DEFAULT NULL
  12.  ,D_DOM INTEGER DEFAULT NULL
  13.  ,D_WOY INTEGER DEFAULT NULL
  14.  ,D_WEEKYEAR INTEGER DEFAULT NULL
  15.  
  16.  ,D_PERIOD INTEGER DEFAULT NULL
  17.  ,D_QOY INTEGER DEFAULT NULL
  18.  ,D_QTRWEEK INTEGER DEFAULT NULL
  19.  
  20.  ,D_FISCAL_YEAR FOR COLUMN D_F_YEAR INTEGER DEFAULT NULL
  21.  ,D_FISCAL_QUARTER FOR COLUMN D_F_QOY INTEGER DEFAULT NULL
  22.  ,D_FISCAL_PERIOD FOR COLUMN D_F_PERIOD INTEGER DEFAULT NULL
  23.  
  24.  ,D_FIRST_DOM FOR COLUMN D_FDOM INTEGER DEFAULT NULL
  25.  ,D_LAST_DOM  FOR COLUMN D_LDOM INTEGER DEFAULT NULL
  26.  ,D_FIRST_DOW FOR COLUMN D_FDOW INTEGER DEFAULT NULL
  27.  ,D_LAST_DOW  FOR COLUMN D_LDOW INTEGER DEFAULT NULL
  28.  ,D_SAME_DAY_LY FOR COLUMN D_LYDAY INTEGER DEFAULT NULL
  29.  ,D_SAME_DAY_LQ FOR COLUMN D_LQDAY INTEGER DEFAULT NULL
  30.  ,D_SAME_WEEK_LY FOR COLUMN D_LYWEEK INTEGER DEFAULT NULL
  31.  
  32.  ,D_DAY_NAME CHAR(9) CCSID 37 DEFAULT NULL
  33.  ,D_MONTH_NAME FOR COLUMN D_MOYN CHAR(9) CCSID 37 DEFAULT NULL
  34.  ,D_MONTH_ABRV FOR COLUMN D_MOYNA CHAR(3) CCSID 37 DEFAULT NULL
  35.  ,D_MONTH_YEAR FOR COLUMN D_MNYR CHAR(8) CCSID 37 DEFAULT NULL
  36.  ,D_QUARTER_NAME  FOR COLUMN D_QOYN CHAR(7) CCSID 37 DEFAULT NULL
  37.  ,D_FISCAL_QUARTER_NAME FOR COLUMN D_F_QOYN CHAR(7) CCSID 37 DEFAULT NULL
  38.  
  39.  ,D_HOLIDAY CHAR(1) CCSID 37 DEFAULT NULL
  40.  ,D_HOLIDAY_NAME FOR COLUMN D_HOLNAME Varchar(50) CCSID 37 DEFAULT NULL
  41.  ,D_FOLLOWING_HOLIDAY FOR COLUMN D_FOLOWHOL CHAR(1) CCSID 37 DEFAULT NULL
  42.  ,D_WEEKEND CHAR(1) CCSID 37 DEFAULT NULL
  43.  ,D_MONTH_FIRST_SAT FOR COLUMN D_FIRSTSAT CHAR(1) CCSID 37 DEFAULT NULL
  44.  ,D_MONTH_FIRST_FULL_WEEK FOR COLUMN D_FIRSTFW CHAR(1) CCSID 37 DEFAULT NULL
  45.  ,D_MONTH_LAST_SAT FOR COLUMN D_LASTSAT CHAR(1) CCSID 37 DEFAULT NULL
  46.  ,D_MONTH_LAST_FULL_WEEK FOR COLUMN D_LASTFW CHAR(1) CCSID 37 DEFAULT NULL
  47.  
  48.  ,D_CURRENT_13_WEEKS FOR COLUMN D_CUR13WK CHAR(1) CCSID 37 DEFAULT NULL
  49.  
  50.  ,D_DATEYYMD DECIMAL(8, 0) DEFAULT NULL
  51.  ,D_DATEMDYY DECIMAL(8, 0) DEFAULT NULL
  52.  ,D_DATECYMD DECIMAL(7, 0) DEFAULT NULL
  53.  ,D_JULIAN DECIMAL(7, 0) DEFAULT NULL
  54.  ,D_CENTURY DECIMAL(2, 0) DEFAULT NULL
  55.  ,D_YEAR2 DECIMAL(2, 0) DEFAULT NULL
  56. );
  57.  
  58. LABEL ON TABLE DATE_DIM
  59.  IS 'Date datastructure of all types of date values';
  60.  
  61. LABEL ON COLUMN DATE_DIM
  62. (
  63.  D_DATE_SK      is 'Date Unique         Seq Key'
  64. ,D_DATE         is 'Date'
  65. ,D_MONTH_SEQ    is 'Month               Seq Key'
  66. ,D_WEEK_SEQ     is 'Week                Seq Key'
  67. ,D_QUARTER_SEQ  is 'Quarter             Seq Key'
  68. ,D_PERIOD_SEQ   is 'Period              Seq Key'
  69. ,D_YEAR         is 'Year'
  70. ,D_DOW          is 'Day of              Week'
  71. ,D_DAYOFWEEKINMONTH is 'Day of              Week In             Month'
  72. ,D_MOY          is 'Month of            Year'
  73. ,D_DOM          is 'Day of              Month'
  74. ,D_WOY          is 'Week of             Year'
  75. ,D_WEEKYEAR     is 'Year for            Week'
  76. ,D_PERIOD       is 'Period              Year'
  77. ,D_QOY          is 'Quarter of          Year'
  78. ,D_QTRWEEK      is 'Quarter week of     Year'
  79. ,D_FISCAL_YEAR  is 'Fiscal              Year'
  80. ,D_FISCAL_QUARTER is 'Fiscal              Quarter'
  81. ,D_FISCAL_PERIOD  is 'Fiscal              Period'
  82. ,D_FIRST_DOM    is 'First               Day of              Month Id'
  83. ,D_LAST_DOM     is 'Last                Day of              Month Id'
  84. ,D_FIRST_DOW    is 'First               Day of              Week Id'
  85. ,D_LAST_DOW     is 'Last                Day of              Week Id'
  86. ,D_SAME_DAY_LY  is 'Same Day            Last                Year Id'
  87. ,D_SAME_DAY_LQ  is 'Same Day            Last                Quarter Id'
  88. ,D_SAME_WEEK_LY is 'Same Week           Last                Year Id'
  89. ,D_DAY_NAME     is 'Day Name '
  90. ,D_MONTH_NAME   is 'Month Name'
  91. ,D_MONTH_ABRV   is 'Month               Abbrv'
  92. ,D_MONTH_YEAR   is 'Month Year          Name'
  93. ,D_QUARTER_NAME is 'Quarter             Name'
  94. ,D_FISCAL_QUARTER_NAME is 'Fiscal              Quarter             Name'
  95. ,D_HOLIDAY      is 'Is a                Holiday'
  96. ,D_HOLIDAY_NAME is 'Name of Holiday'
  97. ,D_FOLLOWING_HOLIDAY is 'Follows             a Holiday'
  98. ,D_WEEKEND      is 'Is a                Weekend'
  99. ,D_MONTH_FIRST_SAT is 'First               Saturday            of Month'
  100. ,D_MONTH_FIRST_FULL_WEEK is 'First               Full Week           of Month'
  101. ,D_MONTH_LAST_SAT is 'Last                Saturday            of Month'
  102. ,D_MONTH_LAST_FULL_WEEK is 'Last                Full Week           of Month'
  103. ,D_CURRENT_13_WEEKS is 'Day In              Current             13 Weeks'
  104. ,D_DATEYYMD     is 'Year                Month Day           Date'
  105. ,D_DATEMDYY     is 'Month Day           Year                Date'
  106. ,D_DATECYMD     is 'Century Year        Month Day           Date'
  107. ,D_JULIAN       is 'Julian              Date'
  108. ,D_CENTURY      is 'Century'
  109. ,D_YEAR2        is 'Year'
  110. ) ;
  111.  
  112. LABEL ON COLUMN DATE_DIM
  113. (
  114.  D_DATE_SK           text is 'Date Unique Seq Key'
  115. ,D_DATE              text is 'The Date'
  116. ,D_MONTH_SEQ         text is 'Month Seq Key'
  117. ,D_WEEK_SEQ          text is 'Week Seq Key'
  118. ,D_QUARTER_SEQ       text is 'Quarter Seq Key'
  119. ,D_PERIOD_SEQ        text is 'Period Seq Key'
  120. ,D_YEAR              text is 'Year'
  121. ,D_DOW               text is 'Day of Week'
  122. ,D_DAYOFWEEKINMONTH  text is 'Day of Week In Month'
  123. ,D_MOY               text is 'Month of Year'
  124. ,D_DOM               text is 'Day of Month'
  125. ,D_WOY               text is 'Week of Year'
  126. ,D_WEEKYEAR          text is 'Year for week when different than day year'
  127. ,D_PERIOD            text is 'Period Year'
  128. ,D_QOY               text is 'Quarter of Year'
  129. ,D_QTRWEEK           text is 'Quarter week of Year'
  130. ,D_FISCAL_YEAR       text is 'Fiscal Year'
  131. ,D_FISCAL_QUARTER    text is 'Fiscal Quarter'
  132. ,D_FISCAL_PERIOD     text is 'Fiscal Period'
  133. ,D_FIRST_DOM         text is 'First Day of Month Index Entry'
  134. ,D_LAST_DOM          text is 'Last Day of Month Index Entry'
  135. ,D_FIRST_DOW         text is 'First Day of Week Index Entry'
  136. ,D_LAST_DOW          text is 'Last Day of Week Index Entry'
  137. ,D_SAME_DAY_LY       text is 'Same Day Last Year Index Entry'
  138. ,D_SAME_DAY_LQ       text is 'Same Day Last Quarter Index Entry'
  139. ,D_SAME_WEEK_LY      text is 'Same Week Last Year Index Entry'
  140. ,D_DAY_NAME          text is 'Day Name '
  141. ,D_MONTH_NAME        text is 'Month Name'
  142. ,D_MONTH_ABRV        text is 'Month Abbrv'
  143. ,D_MONTH_YEAR        text is 'Month Name Selecting'
  144. ,D_QUARTER_NAME      text is 'Quarter Name'
  145. ,D_FISCAL_QUARTER_NAME text is 'Fiscal Quarter Name'
  146. ,D_HOLIDAY           text is 'Is a Holiday'
  147. ,D_HOLIDAY_NAME      text is 'Name of Holiday'
  148. ,D_WEEKEND           text is 'Is a Weekend'
  149. ,D_FOLLOWING_HOLIDAY text is 'Follows a Holiday'
  150. ,D_MONTH_FIRST_SAT   text is 'First Saturday of Month'
  151. ,D_MONTH_FIRST_FULL_WEEK text is 'First Full Week of Month'
  152. ,D_MONTH_LAST_SAT text is 'Last Saturday of Month'
  153. ,D_MONTH_LAST_FULL_WEEK text is 'Last Full Week of Month'
  154. ,D_CURRENT_13_WEEKS  text is 'This is the Current 13 Weeks'
  155. ,D_DATEYYMD          text is 'Numeric Year Month Day Date'
  156. ,D_DATEMDYY          text is 'Numeric Month Day Year Date'
  157. ,D_DATECYMD          text is 'Numeric Century Year Month Day Date'
  158. ,D_JULIAN            text is 'Julian Date'
  159. ,D_CENTURY           text is 'Century'
  160. ,D_YEAR2             text is 'Year'
  161. ) ;
  162.  
  163.  
  164.  
  165.  
  166. Drop Table Qtemp/Date_Dim_dates;
  167. Drop Table Qtemp/Date_Dim_years;
  168. Drop Table Qtemp/Date_Dim_calendar;
  169.  
  170. /* 693596 is the integer you can pass to DATE() in sql to generate '1900-01-01';
  171. /* Build a list of all dates between 1900-01-01 AND 2099-12-31 */
  172. Create Table Qtemp/Date_Dim_Dates
  173.    ( D_DATE_SK, D_DATE,D_MONTH_SEQ, D_WEEK_SEQ)
  174.  AS (
  175.    WITH TEMPDATE (LDATE, LDATEID) AS (
  176.    VALUES( DATE(693596  ), 693596 )
  177.    union select DATE(LDATEID) + 1 DAY, LDATEID+1
  178.    FROM TEMPDATE WHERE DATE(LDATEID) < date('2099-12-31')
  179.    )
  180.    select Distinct LDATEID,  Ldate
  181.    , DENSE_RANK() OVER( ORDER BY YEAR(LDATE), MONTH(LDATE) )
  182.    , DENSE_RANK() OVER( ORDER BY (LDATEID + (7- dayofweek(LDATE))))
  183.    From TEMPDATE
  184.  ) WITH DATA INCLUDING DEFAULTS;
  185.  
  186. /* Build table with all years, Number of Weeks,
  187. Begin Date, and End Date
  188. Here is the first place were you can decide which years get 53 weeks.
  189. */
  190. Create Table Qtemp/Date_Dim_years (D_YEAR, D_YEAR_BEGIN, D_YEAR_END, D_YEAR_WEEKS) As (
  191. With t1 (D_Year) as (
  192.  Select Year(D_DATE) From Qtemp/Date_Dim_Dates Group By Year(D_Date)
  193. ), T2 (D_Year, D_BEG, D_END) As (
  194. Select D_Year
  195. , ((SELECT T2.D_DATE_SK
  196.   + (CASE Dayofweek(T2.D_DATE)
  197.     WHEN 1 THEN -1 WHEN 2 THEN -2 WHEN 3 THEN -3
  198.     WHEN 4 THEN  3 WHEN 5 THEN  2 WHEN 6 THEN  1 WHEN 7 THEN 0 END)
  199.    FROM Qtemp/Date_Dim_Dates T2
  200.    WHERE T2.D_DATE = DATE('12/31/'|| (T1.D_Year-1))
  201.  )+1 )  /* D_YEAR_BEGIN */
  202. , ((SELECT T2.D_DATE_SK
  203.      + (CASE Dayofweek(T2.D_DATE)
  204.      WHEN 1 THEN -1 WHEN 2 THEN -2 WHEN 3 THEN -3
  205.      WHEN 4 THEN 3 WHEN 5 THEN 2 WHEN 6 THEN 1 WHEN 7 THEN 0 END)
  206.    FROM Qtemp/Date_Dim_Dates T2
  207.    WHERE T2.D_DATE = DATE('12/31/'|| (T1.D_Year ))
  208.  )) /* D_YEAR_END */
  209. From T1)
  210. Select D_Year, D_BEG, D_END, ((D_end-(d_beg-1))/7) From T2
  211. ) With Data Including Defaults;
  212.  
  213.  
  214. /* Build table with calendar dates. */
  215. Create Table Qtemp/Date_Dim_calendar
  216. (D_CAL_YEAR, D_CAL_WEEKS
  217. , D_Cal_Week, D_CAL_WEEK_SEQ
  218. , D_CAL_BEGIN, D_CAL_END
  219. , D_PERIOD, D_PERIOD_SEQ
  220. , D_QOY, D_QTRWEEK, D_QUARTER_SEQ) As (
  221. With TempWeek (Weeknumber) as ( VALUES( 1)
  222.  union select Weeknumber + 1 From TempWeek Where Weeknumber < 53 )
  223. , WeekNum (Weeknumber) As (Select Distinct Weeknumber From TempWeek)
  224. , YearWeek (cyear, cweeks) as (
  225.     /* Here is the second place were you can decide which years get 53 weeks. */
  226.  SELECT D_YEAR, D_YEAR_WEEKS
  227.    FROM Qtemp/Date_Dim_years
  228.  Union Values ( 1900,  52))
  229. , T3 (D_CAL_YEAR, D_CAL_WEEKS, D_Cal_Week, D_CAL_WEEK_SEQ) AS (
  230.  Select Cyear, Cweeks, Weeknumber,
  231.  Row_NUmber() Over(Order By Cyear, Weeknumber) As WkIdx
  232.  From YearWeek
  233.  Cross Join WeekNum
  234.  Where Weeknumber <= cweeks)
  235. Select D_CAL_YEAR, D_CAL_WEEKS, D_Cal_Week, D_CAL_WEEK_SEQ
  236. , (Select Min(D_Date_sk)
  237.    From Qtemp/Date_Dim_Dates
  238.    Where D_WEEK_SEQ = T3.D_CAL_WEEK_SEQ)
  239. , (Select Max(D_Date_sk)
  240.    From Qtemp/Date_Dim_Dates
  241.    Where D_WEEK_SEQ = T3.D_CAL_WEEK_SEQ)
  242. , INT(0) D_PERIOD_SEQ, INT(0) D_QOY
  243. , INT(0) AS D_QTRWEEK
  244. , INT(0) AS D_QUARTER_SEQ
  245. , INT(0) AS D_PERIOD_SEQ
  246. From T3
  247. ) With Data Including Defaults;
  248.  
  249.  
  250. Update  Qtemp/Date_Dim_calendar
  251. SET D_PERIOD =
  252. (CASE
  253. WHEN D_CAL_WEEKS = 53 THEN
  254.  (Case When D_Cal_Week In (1,2,3,4) Then 1
  255.  When D_Cal_Week In (5,6,7,8) Then 2
  256.  When D_Cal_Week In (9,10,11,12,13) Then 3
  257.  When D_Cal_Week In (14,15,16,17) Then 4
  258.  When D_Cal_Week In (18,19,20,21,22) Then 5
  259.  When D_Cal_Week In (23,24,25,26,27) Then 6
  260.  When D_Cal_Week In (28,29,30,31) Then 7
  261.  When D_Cal_Week In (32,33,34,35) Then 8
  262.  When D_Cal_Week In (36,37,38,39,40) Then 9
  263.  When D_Cal_Week In (41,42,43,44) Then 10
  264.  When D_Cal_Week In (45,46,47,48) Then 11
  265.  When D_Cal_Week In (49,50,51,52,53) Then 12
  266.  END)
  267.  
  268. ELSE /*WHEN D_CAL_WEEKS = 52 THEN*/
  269.  (Case When D_Cal_Week In (1,2,3,4) Then 1
  270.  When D_Cal_Week In (5,6,7,8) Then 2
  271.  When D_Cal_Week In (9,10,11,12,13) Then 3
  272.  When D_Cal_Week In (14,15,16,17) Then 4
  273.  When D_Cal_Week In (18,19,20,21) Then 5
  274.  When D_Cal_Week In (22,23,24,25,26) Then 6
  275.  When D_Cal_Week In (27,28,29,30) Then 7
  276.  When D_Cal_Week In (31,32,33,34) Then 8
  277.  When D_Cal_Week In (35,36,37,38,39) Then 9
  278.  When D_Cal_Week In (40,41,42,43) Then 10
  279.  When D_Cal_Week In (44,45,46,47) Then 11
  280.  When D_Cal_Week In (48,49,50,51,52) Then 12
  281.  END)
  282. END)
  283. WHERE D_PERIOD = 0;
  284.  
  285. UPDATE Qtemp/Date_Dim_calendar
  286. SET D_QOY = (Case When D_PERIOD >= 10 Then 4
  287.  When D_PERIOD >= 7 Then 3
  288.  When D_PERIOD >= 4 Then 2
  289.  Else 1
  290.  End)
  291. Where D_QOY = 0;
  292.  
  293.  
  294. MERGE INTO Qtemp/Date_Dim_calendar T1
  295.  USING (
  296.    Select D_CAL_WEEK_SEQ
  297.  , Row_number()
  298.    Over(Partition By D_CAL_YEAR, D_QOY
  299.         Order By D_CAL_WEEK_SEQ)
  300.  , Dense_rank() Over(Order By D_CAL_YEAR,D_QOY )
  301.  , Dense_rank() Over(Order By D_CAL_YEAR,D_PERIOD )
  302.  from Qtemp/Date_Dim_calendar
  303.  ) T2 (D_CAL_WEEK_SEQ, D_QTRWEEK, D_QUARTER_SEQ, D_PERIOD_SEQ)
  304.  ON T1.D_CAL_WEEK_SEQ=T2.D_CAL_WEEK_SEQ
  305.  WHEN MATCHED THEN
  306.  UPDATE SET T1.D_QTRWEEK = T2.D_QTRWEEK,
  307.    T1.D_QUARTER_SEQ= T2.D_QUARTER_SEQ,
  308.    T1.D_PERIOD_SEQ=T2.D_PERIOD_SEQ;
  309.  
  310.  
  311. Insert Into DATE_DIM (  D_DATE_SK
  312. ,D_DATE
  313. ,D_MONTH_SEQ
  314. ,D_WEEK_SEQ
  315. ,D_QUARTER_SEQ
  316. ,D_PERIOD_SEQ
  317. ,D_YEAR
  318. ,D_DOW
  319. ,D_DAYOFWEEKINMONTH
  320. ,D_MOY
  321. ,D_DOM
  322. ,D_WOY
  323. ,D_WEEKYEAR
  324.  
  325. ,D_PERIOD
  326. ,D_QOY
  327. ,D_QTRWEEK
  328.  
  329. ,D_FIRST_DOM
  330. ,D_LAST_DOM
  331. ,D_FIRST_DOW
  332. ,D_LAST_DOW
  333.  
  334. ,D_DAY_NAME
  335. ,D_MONTH_NAME
  336. ,D_MONTH_ABRV
  337. ,D_MONTH_YEAR
  338. ,D_QUARTER_NAME
  339. ,D_FISCAL_QUARTER_NAME
  340.  
  341. ,D_WEEKEND
  342. ,D_MONTH_FIRST_SAT
  343. ,D_MONTH_FIRST_FULL_WEEK
  344. ,D_MONTH_LAST_SAT
  345. ,D_MONTH_LAST_FULL_WEEK
  346.  
  347. ,D_DATEYYMD
  348. ,D_DATEMDYY
  349. ,D_DATECYMD
  350. ,D_JULIAN
  351. ,D_CENTURY
  352. ,D_Year2
  353. )
  354. SELECT T1.D_DATE_SK
  355. , T1.D_DATE
  356. , T1.D_MONTH_SEQ
  357. , T1.D_WEEK_SEQ
  358. , T3.D_QUARTER_SEQ
  359. , T3.D_PERIOD_SEQ
  360. , YEAR(T1.D_DATE) D_YEAR
  361. , DAYOFWEEK(T1.D_DATE) D_DOW
  362. , (FLOOR(DAY(T1.D_DATE)/7) + CASE WHEN MOD(DAY(T1.D_DATE),7) > 0 THEN 1 ELSE 0 END) D_DAYOFWEEKINMONTH
  363. , MONTH(T1.D_DATE) D_MOY
  364. , DAY(T1.D_DATE) D_DOM
  365. , T3.D_Cal_Week D_WOY
  366. , T3.D_cal_year D_WEEKYEAR
  367. , T3.D_PERIOD
  368. , T3.D_QOY
  369. , T3.D_QTRWEEK
  370.  
  371. , (T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE) +1) D_FIRST_DOM
  372. , (T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE) + DAYOFMONTH(LAST_DAY(T1.D_DATE))) D_LAST_DOM
  373. , (T1.D_DATE_SK- DAYOFWEEK(T1.D_DATE) +1) D_FIRST_DOW
  374. , (T1.D_DATE_SK- DAYOFWEEK(T1.D_DATE) +7) D_LAST_DOW
  375.  
  376. , CAST(DAYNAME(T1.D_DATE) AS CHAR(9)) D_DAY_NAME
  377. , MONTHNAME(T1.D_DATE) D_MONTH_NAME
  378. , UCASE(Substr(Monthname(T1.D_DATE), 1,3)) D_MONTH_ABRV
  379. , UCASE(Substr(Monthname(T1.D_DATE), 1,3)) ||' '|| YEAR(T1.D_DATE) D_MONTH_YEAR
  380. , ('Q' || DIGITS(DEC(T3.D_QOY,1,0)) || ' '|| T3.D_CAL_YEAR ) D_QUARTER_NAME
  381.  
  382. , CASE WHEN DAYOFWEEK(T1.D_DATE) IN (1,7) THEN 'Y' END D_WEEKEND
  383. , CASE WHEN DAY(T1.D_DATE) < 8
  384.  AND DAYOFWEEK(T1.D_DATE) = 7
  385.  THEN 'Y' END D_MONTH_FIRST_SAT
  386. , (
  387. /* IF the first day of the month is dow 1 then week 1, else week 2 */
  388.  CASE WHEN DAYOFWEEK(DATE(T1.D_DATE_SK-DAYOFMONTH(T1.D_DATE)+1))= 1
  389.     AND WEEK(DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE)+1))
  390.     =WEEK(T1.D_DATE)
  391.   THEN 'Y'
  392.   WHEN DAYOFWEEK(DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE) +1)) > 1
  393.     AND (WEEK(DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE) +1))+1)
  394.     =WEEK(T1.D_DATE) THEN 'Y'
  395.   END) D_MONTH_FIRST_FULL_WEEK
  396. , (CASE WHEN DAY(T1.D_DATE) > DAYOFMONTH(LAST_DAY(T1.D_DATE))-7
  397.     AND DAYOFWEEK(T1.D_DATE) = 7
  398.   THEN 'Y'
  399.   END) D_MONTH_LAST_SAT
  400. , (
  401. /* IF the LAST day of the month is dow 7 then LAST week, else LAST WEEK-1 */
  402.  CASE WHEN DAYOFWEEK( DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE)
  403.    + DAYOFMONTH(LAST_DAY(T1.D_DATE))))=7
  404.    AND WEEK(DATE(T1.D_DATE_SK -DAYOFMONTH(T1.D_DATE)
  405.    + DAYOFMONTH(LAST_DAY(T1.D_DATE))))=WEEK(T1.D_DATE)
  406.  THEN 'Y'
  407.  WHEN DAYOFWEEK(DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE)
  408.    + DAYOFMONTH(LAST_DAY(T1.D_DATE)))) < 7
  409.    AND (WEEK(DATE(T1.D_DATE_SK - DAYOFMONTH(T1.D_DATE)
  410.    + DAYOFMONTH(LAST_DAY(T1.D_DATE))))-1)=WEEK(T1.D_DATE)
  411.  THEN 'Y'
  412.  END) D_MONTH_LAST_FULL_WEEK
  413.  
  414. ,(YEAR(T1.D_DATE)*10000
  415.  +DEC(MONTH(T1.D_DATE),2,0)*100
  416.  +DEC(DAY(T1.D_DATE),2,0)) D_DATEYYMD
  417.  
  418. , (DEC(MONTH(T1.D_DATE),2,0)*1000000
  419.  +DEC(DAY(T1.D_DATE),2,0)*10000
  420.  +YEAR(T1.D_DATE)) D_DATEMDYY
  421.  
  422. ,((YEAR(T1.D_DATE)-1900)*10000
  423.  +DEC(MONTH(T1.D_DATE),2,0)*100
  424.  +DEC(DAY(T1.D_DATE),2,0)) D_DATECYMD
  425.  
  426. , (DEC( YEAR(T1.D_DATE) *1000 + DAYOFYEAR(T1.D_DATE) ,7,0))  D_JULIAN
  427. , DEC(SUBSTR(DIGITS(DEC(YEAR(T1.D_DATE),4,0)),1,2),2,0) D_CENTURY
  428. , DEC(SUBSTR(DIGITS(DEC(YEAR(T1.D_DATE),4,0)),3,2),2,0) D_Year2
  429.  
  430.  From Qtemp/Date_Dim_dates T1
  431.  Join Qtemp/Date_Dim_calendar T3
  432.  On T1.D_date_sk Between T3.D_CAL_BEGIN And T3.D_CAL_END;
  433.  
  434.  
  435.  
  436. /* Holidays are rather *fun* to load */
  437. Update Date_Dim
  438.    Set D_HOLIDAY_NAME ='Thanksgiving Day'
  439.  Where D_MOY = 11
  440.  And D_DAYOFWEEKINMONTH = 4
  441.  And D_DOW = 5;
  442.  
  443. Update Date_Dim
  444.    Set D_HOLIDAY_NAME ='Christmas Day'
  445.  Where D_MOY = 12
  446.  And D_DOM = 25;
  447.  
  448. Update Date_Dim
  449.    Set D_HOLIDAY_NAME ='Independance Day'
  450.  Where D_MOY = 7
  451.  And D_DOM = 4;
  452.  
  453. Update Date_Dim
  454.    Set D_HOLIDAY_NAME = 'New Year''s Day'
  455.  Where D_MOY = 1
  456.  And D_DOM = 1;
  457.  
  458. Update Date_Dim
  459.    Set D_HOLIDAY_NAME = 'Memorial Day'
  460.  Where D_Date_Sk In (
  461.    Select Max(D_Date_Sk)
  462.    From Date_Dim
  463.    Where D_MOY = 5 And D_DOW = 2
  464.    Group By D_Year, D_Moy);
  465.  
  466. /* Labor Day - First Monday in September */
  467. Update Date_Dim
  468.    Set D_HOLIDAY_NAME = 'Labor Day'
  469.  Where D_Date_Sk In (
  470.    Select Max(D_Date_Sk)
  471.    From Date_Dim
  472.    Where D_MOY = 9 And D_DOW = 2
  473.    Group By D_Year, D_Moy);
  474.  
  475. Update Date_Dim
  476.    Set D_HOLIDAY_NAME = 'Valentine''s Day'
  477.  Where D_MOY = 2 And D_DOM = 14;
  478.  
  479. Update Date_Dim
  480.    Set D_HOLIDAY_NAME = 'Saint Patrick''s Day'
  481.  Where D_MOY = 3 And D_DOM = 17;
  482.  
  483. Update Date_Dim
  484.    Set D_HOLIDAY_NAME = 'Martin Luthor King Jr Day'
  485.  Where D_MOY = 1 And D_DOW = 2 And D_Year >= 1983
  486.  And D_DAYOFWEEKINMONTH = 3;
  487.  
  488. Update Date_Dim
  489.    Set D_HOLIDAY_NAME = 'President''s Day'
  490.  Where D_MOY = 2 And D_DOW = 2
  491.  And D_DAYOFWEEKINMONTH = 3;
  492.  
  493. Update Date_Dim
  494.    Set D_HOLIDAY_NAME = 'Mother''s Day'
  495.  Where D_MOY = 5 And D_DOW = 1
  496.  And D_DAYOFWEEKINMONTH = 2;
  497.  
  498. Update Date_Dim
  499.    Set D_HOLIDAY_NAME = 'Father''s Day'
  500.  Where D_MOY = 6 And D_DOW = 1
  501.  And D_DAYOFWEEKINMONTH = 3;
  502.  
  503. Update Date_Dim
  504.    Set D_HOLIDAY_NAME = 'Halloween'
  505.  Where D_MOY = 10 And D_DOM = 31;
  506.  
  507. /* Election Day - The first Tuesday after the first Monday in November*/
  508. Update Date_Dim
  509.    Set D_HOLIDAY_NAME = 'Election Day'    
  510.  Where D_Date_Sk In (
  511.    Select Max(D_Date_Sk)+1
  512.    From Date_Dim
  513.    Where D_MOY = 11 And D_DOW = 2
  514.    And D_DAYOFWEEKINMONTH = 1
  515.    Group By D_Year, D_Moy);
  516.  
  517. Update Date_Dim
  518.    Set D_HOLIDAY_NAME = 'April Fools'' Day'
  519.  Where D_MOY = 4 And D_DOM = 1;
  520.  
  521. /* Easter is fun
  522.   These are the dates copied from this webpage.
  523.   http://www.timeanddate.com/holidays/us/easter-sunday
  524.  */
  525.  
  526.  Exec Sql Update Date_Dim
  527.    Set D_HOLIDAY_NAME = 'Easter'
  528.  Where D_Date In
  529.   ('1900-04-15' ,'1901-04-07' ,'1902-03-30' ,'1903-04-12'
  530.    ,'1904-04-03' ,'1905-04-23' ,'1906-04-15' ,'1907-03-31'
  531.    ,'1908-04-19' ,'1909-04-11' ,'1910-03-27' ,'1911-04-16'
  532.    ,'1912-04-07' ,'1913-03-23' ,'1914-04-12' ,'1915-04-04'
  533.    ,'1916-04-23' ,'1917-04-08' ,'1918-03-31' ,'1919-04-20'
  534.    ,'1920-04-04' ,'1921-03-27' ,'1922-04-16' ,'1923-04-01'
  535.    ,'1924-04-20' ,'1925-04-12' ,'1926-04-04' ,'1927-04-17'
  536.    ,'1928-04-08' ,'1929-03-31' ,'1930-04-20' ,'1931-04-05'
  537.    ,'1932-03-27' ,'1933-04-16' ,'1934-04-01' ,'1935-04-21'
  538.    ,'1936-04-12' ,'1937-03-28' ,'1938-04-17' ,'1939-04-09'
  539.    ,'1940-03-24' ,'1941-04-13' ,'1942-04-05' ,'1943-04-25'
  540.    ,'1944-04-09' ,'1945-04-01' ,'1946-04-21' ,'1947-04-06'
  541.    ,'1948-03-28' ,'1949-04-17' ,'1950-04-09' ,'1951-03-25'
  542.    ,'1952-04-13' ,'1953-04-05' ,'1954-04-18' ,'1955-04-10'
  543.    ,'1956-04-01' ,'1957-04-21' ,'1958-04-06' ,'1959-03-29'
  544.    ,'1960-04-17' ,'1961-04-02' ,'1962-04-22' ,'1963-04-14'
  545.    ,'1964-03-29' ,'1965-04-18' ,'1966-04-10' ,'1967-03-26'
  546.    ,'1968-04-14' ,'1969-04-06' ,'1970-03-29' ,'1971-04-11'
  547.    ,'1972-04-02' ,'1973-04-22' ,'1974-04-14' ,'1975-03-30'
  548.    ,'1976-04-18' ,'1977-04-10' ,'1978-03-26' ,'1979-04-15'
  549.    ,'1980-04-06' ,'1981-04-19' ,'1982-04-11' ,'1983-04-03'
  550.    ,'1984-04-22' ,'1985-04-07' ,'1986-03-30' ,'1987-04-19'
  551.    ,'1988-04-03' ,'1989-03-26' ,'1990-04-15' ,'1991-03-31'
  552.    ,'1992-04-19' ,'1993-04-11' ,'1994-04-03' ,'1995-04-16'
  553.    ,'1996-04-07' ,'1997-03-30' ,'1998-04-12' ,'1999-04-04'
  554.    ,'2000-04-23' ,'2001-04-15' ,'2002-03-31' ,'2003-04-20'
  555.    ,'2004-04-11' ,'2005-03-27' ,'2006-04-16' ,'2007-04-08'
  556.    ,'2008-03-23' ,'2009-04-12' ,'2010-04-04' ,'2011-04-24'
  557.    ,'2012-04-08' ,'2013-03-31' ,'2014-04-20' ,'2015-04-05'
  558.    ,'2016-03-27' ,'2017-04-16' ,'2018-04-01' ,'2019-04-21'
  559.    ,'2020-04-12' ,'2021-04-04' ,'2022-04-17' ,'2023-04-09'
  560.    ,'2024-03-31' ,'2025-04-20' ,'2026-04-05' ,'2027-03-28'
  561.    ,'2028-04-16' ,'2029-04-01' ,'2030-04-21' ,'2031-04-13'
  562.    ,'2032-03-28' ,'2033-04-17' ,'2034-04-09' ,'2035-03-25'
  563.    ,'2036-04-13' ,'2037-04-05' ,'2038-04-25' ,'2039-04-10'
  564.    ,'2040-04-01' ,'2041-04-21' ,'2042-04-06' ,'2043-03-29'
  565.    ,'2044-04-17' ,'2045-04-09' ,'2046-03-25' ,'2047-04-14'
  566.    ,'2048-04-05' ,'2049-04-18' ,'2050-04-10' ,'2051-04-02'
  567.    ,'2052-04-21' ,'2053-04-06' ,'2054-03-29' ,'2055-04-18'
  568.    ,'2056-04-02' ,'2057-04-22' ,'2058-04-14' ,'2059-03-30'
  569.    ,'2060-04-18' ,'2061-04-10' ,'2062-03-26' ,'2063-04-15'
  570.    ,'2064-04-06' ,'2065-03-29' ,'2066-04-11' ,'2067-04-03'
  571.    ,'2068-04-22' ,'2069-04-14' ,'2070-03-30' ,'2071-04-19'
  572.    ,'2072-04-10' ,'2073-03-26' ,'2074-04-15' ,'2075-04-07'
  573.    ,'2076-04-19' ,'2077-04-11' ,'2078-04-03' ,'2079-04-23'
  574.    ,'2080-04-07' ,'2081-03-30' ,'2082-04-19' ,'2083-04-04'
  575.    ,'2084-03-26' ,'2085-04-15' ,'2086-03-31' ,'2087-04-20'
  576.    ,'2088-04-11' ,'2089-04-03' ,'2090-04-16' ,'2091-04-08'
  577.    ,'2092-03-30' ,'2093-04-12' ,'2094-04-04' ,'2095-04-24'
  578.    ,'2096-04-15' ,'2097-03-31' ,'2098-04-20' ,'2099-04-12')
  579.  ;
  580.  
  581.  
  582. Update Date_DIm
  583.      Set D_HOLIDAY = CASE
  584.        WHEN D_Holiday_Name is null then null
  585.        Else 'Y' End;
  586.  
© 2004-2019 by midrange.com generated in 0.29s valid xhtml & css