midrange.com -- source code scratchpad

midrange.com code scratchpad
Name:
DATE_DIM creation
Scriptlanguage:
Plain Text
Tabwidth:
Date:
01/20/2016 10:49:15 pm
IP:
Logged
Description:
Builds the DATE_DIM table
Code:

           //========================================================================*

           // PROGRAM NAME...: DATE_DIMCV                      TEMPLATE: STD10BRG

           // AUTHOR.........: Matt Tyler

           // DATE...........: 05/04/2015

           // PCR #..........: 13539 00

           // FUNCTION/DESC..: Build DATE_DIM records

           //------------------------------------------------------------------------

           //    MODIFICATIONS:

           //------------------------------------------------------------------------

           // MOD#  PCR#    PGMR   DATE   DESCRIPTION

           // /M01               MM/DD/YY Changes...

           //========================================================================*

         H EXTBININT( *YES )

          /COPY QCPYSRC,HSPECLE

     
           DCL-F PRINT PRINTER(132) USAGE(*OUTPUT) OFLIND(*INOF);

     
          // Prototypes

          ***/COPY QCPYSRC,QCMDEXCPR

          ***/COPY QCPYSRC,MSGTOOLSCL

          ***/COPY QCPYSRC,UTL10ARGPR

          ***/COPY QCPYSRC,UTL25ACLPR

     
          // General tools

          ***/COPY QCPYSRC,UTL000RGPR

          ***/COPY QCPYSRC,SQLUDFPR

     
          // Data Structures

          ***/COPY QCPYSRC,DSPGMINF4

          ***

          // Result Set Container

           DCL-DS RS_A EXTNAME( 'DATE_DIM' ) INZ ALIAS;

           END-DS;

     
          //----------------------------------------------------------------------

          // Stand Alone Fields

          //----------------------------------------------------------------------

           DCL-S EOF IND;

           DCL-S ERRINDSQL IND;

           DCL-S workfield varchar(100);

     
          //----------------------------------------------------------------------

          // Mainline

          //----------------------------------------------------------------------

          //  This SQL statment is REQUIRED for this program to run correctly.

     
           // Set SQL Options

           EXEC SQL SET OPTION DATFMT = *ISO

                              ,TIMFMT = *ISO

                              ,ALWCPYDTA = *OPTIMIZE

                              ,CLOSQLCSR = *ENDMOD

                              ,COMMIT = *NONE

                              ,DLYPRP = *YES;

     
           EXSR PROCESS;

           EXSR SetCurrent13Weeks;

           EXSR AddHolidays;

     
           EXSR EXIT;

     
     
           //---------------------------------------------------------------------

           // SQL error processing

           //---------------------------------------------------------------------

           BEGSR ERRORSQL;

     
             ERRINDSQL = *ON;

     
           //////  EVALR MSGTOOLS.ID = %EDITW(SQLCODE:'     0    ');

           //////  IF %SUBST(MSGTOOLS.ID:3:1) = *BLANK;

           //////    %SUBST(MSGTOOLS.ID:1:3)='SQL';

           //////  ELSE;

           //////    %SUBST(MSGTOOLS.ID:1:2) ='SQ';

           //////  ENDIF;

           //////

           //////  MSGTOOLS.DATA = MSGTOOLS.ID + ' (' + SQLSTT +') '

           //////    + 'Please contact the Helpdesk.';

           //////  EXCEPT ERRORSQLP;

           //////

           //////  MSGTOOLS.DATA = MSGRTVLVL1(MSGTOOLS.ID: SQLERRMC: 'QSQLMSG');

           //////  MSGTOOLS.DATA = MSGTOOLS.ID + ' (' + SQLSTT + ') '

           //////    + %TRIM(MSGTOOLS.DATA);

           //////  EXCEPT ERRORSQLP;

           //////

           ENDSR;

           //---------------------------------------------------------------------

           // Exit the program

           //---------------------------------------------------------------------

           BEGSR EXIT;

     
             *INLR = *ON;

             RETURN;

     
           ENDSR;

     
           //---------------------------------------------------------------------

           // Process

           //---------------------------------------------------------------------

           BEGSR PROCESS;

     
             Exec sql Drop Table Qtemp/Date_Dim_dates;

             Exec sql Drop Table Qtemp/Date_Dim_fiscal;

             Exec sql Drop Table Qtemp/Date_Dim_calendar;

     
             // 693596 is the integer you can pass to DATE() in sql

             // to generate '1900-01-01';

     
             // Build a list of all dates between 1900-01-01 AND 2099-12-31

             Exec sql Create Table Qtemp/Date_Dim_Dates

               ( D_DATE_SK, D_DATE,D_MONTH_SEQ, D_WEEK_SEQ)

             AS (

               WITH TEMPDATE (LDATE, LDATEID) AS (

               VALUES( DATE(693596  ), 693596 )

               union select DATE(LDATEID) + 1 DAY, LDATEID+1

               FROM TEMPDATE WHERE DATE(LDATEID) < date('2099-12-31')

               )

               select Distinct LDATEID,  Ldate

               , DENSE_RANK() OVER( ORDER BY YEAR(LDATE), MONTH(LDATE) )

               , DENSE_RANK() OVER( ORDER BY (LDATEID + (7- dayofweek(LDATE))))

               From TEMPDATE

             ) WITH DATA INCLUDING DEFAULTS;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             // Build table with all fiscal years, Number of Weeks,

             //  Begin Date, and End Date

             Exec Sql Create Table Qtemp/Date_Dim_Fiscal

               (D_FISCAL_YEAR, D_FISCAL_BEGIN, D_FISCAL_END, D_FISCAL_WEEKS) As (

               With t1 (D_Year) as (

                 Select Year(D_DATE) From Qtemp/Date_Dim_Dates Group By Year(D_Date)

               ), T2 (D_Year, D_BEG, D_END) As (

               Select D_Year

               , ((SELECT T2.D_DATE_SK

                  + (CASE Dayofweek(T2.D_DATE)

                    WHEN 1 THEN -1 WHEN 2 THEN -2 WHEN 3 THEN -3

                    WHEN 4 THEN  3 WHEN 5 THEN  2 WHEN 6 THEN  1 WHEN 7 THEN 0 END)

                   FROM Qtemp/Date_Dim_Dates T2

                   WHERE T2.D_DATE = DATE('03/31/'|| T1.D_Year)

                 )+1 )  /* D_FISCAL_BEGIN */

               , ((SELECT T2.D_DATE_SK

                     + (CASE Dayofweek(T2.D_DATE)

                     WHEN 1 THEN -1 WHEN 2 THEN -2 WHEN 3 THEN -3

                     WHEN 4 THEN 3 WHEN 5 THEN 2 WHEN 6 THEN 1 WHEN 7 THEN 0 END)

                   FROM Qtemp/Date_Dim_Dates T2

                   WHERE T2.D_DATE = DATE('03/31/'|| (T1.D_Year+1))

                 )) /* D_FISCAL_END */

               From T1)

               Select D_Year, D_BEG, D_END, ((D_end-(d_beg-1))/7) From T2

               ) With Data Including Defaults

               ;;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             // Build table with calendar dates.

             Exec Sql Create Table Qtemp/Date_Dim_calendar

             (D_CAL_YEAR, D_CAL_WEEKS

             , D_Cal_Week, D_CAL_WEEK_SEQ

             , D_CAL_BEGIN, D_CAL_END

             , D_PERIOD, D_PERIOD_SEQ

             , D_QOY, D_QTRWEEK, D_QUARTER_SEQ) As (

               With TempWeek (Weeknumber) as ( VALUES( 1)

                 union select Weeknumber + 1 From TempWeek Where Weeknumber < 53 )

               , WeekNum (Weeknumber) As (Select Distinct Weeknumber From TempWeek)

               , YearWeek (cyear, cweeks) as (

                 SELECT D_FISCAL_YEAR, D_FISCAL_WEEKS

                   FROM Qtemp/Date_Dim_Fiscal

                   WHERE D_FISCAL_YEAR IN (1998,1999,2003,2004)

                 Union SELECT D_FISCAL_YEAR+1, D_FISCAL_WEEKS

                   FROM Qtemp/Date_Dim_Fiscal

                   WHERE D_FISCAL_YEAR NOT IN (1997,1998,2002,2003)

                 Union Values ( 1900,  52))

               , T3 (D_CAL_YEAR, D_CAL_WEEKS, D_Cal_Week, D_CAL_WEEK_SEQ) AS (

                 Select Cyear, Cweeks, Weeknumber,

                 Row_NUmber() Over(Order By Cyear, Weeknumber) As WkIdx

                 From YearWeek

                 Cross Join WeekNum

                 Where Weeknumber <= cweeks)

              Select D_CAL_YEAR, D_CAL_WEEKS, D_Cal_Week, D_CAL_WEEK_SEQ

              , (Select Min(D_Date_sk)

                   From Qtemp/Date_Dim_Dates

                   Where D_WEEK_SEQ = T3.D_CAL_WEEK_SEQ)

              , (Select Max(D_Date_sk)

                   From Qtemp/Date_Dim_Dates

                   Where D_WEEK_SEQ = T3.D_CAL_WEEK_SEQ)

              , INT(0) D_PERIOD, INT(0) D_QOY

              , INT(0) AS D_QTRWEEK

              , INT(0) AS D_QUARTER_SEQ

              , INT(0) AS D_PERIOD_SEQ

              From T3

              ) With Data Including Defaults;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec sql Update  Qtemp/Date_Dim_calendar

               SET D_PERIOD =

               (CASE WHEN D_CAL_YEAR >= 2010 AND D_CAL_WEEKS = 53  THEN

                 (Case When D_Cal_Week In (1,2,3,4) Then 1

                 When D_Cal_Week In (5,6,7,8) Then 2

                 When D_Cal_Week In (9,10,11,12,13,14) Then 3

                 When D_Cal_Week In (15,16,17,18) Then 4

                 When D_Cal_Week In (19,20,21,22) Then 5

                 When D_Cal_Week In (23,24,25,26,27) Then 6

                 When D_Cal_Week In (28,29,30,31) Then 7

                 When D_Cal_Week In (32,33,34,35) Then 8

                 When D_Cal_Week In (36,37,38,39,40) Then 9

                 When D_Cal_Week In (41,42,43,44) Then 10

                 When D_Cal_Week In (45,46,47,48) Then 11

                 When D_Cal_Week In (49,50,51,52,53) Then 12

                 END)

               WHEN D_CAL_WEEKS = 53 THEN

                 (Case When D_Cal_Week In (1,2,3,4) Then 1

                 When D_Cal_Week In (5,6,7,8) Then 2

                 When D_Cal_Week In (9,10,11,12,13) Then 3

                 When D_Cal_Week In (14,15,16,17) Then 4

                 When D_Cal_Week In (18,19,20,21,22) Then 5

                 When D_Cal_Week In (23,24,25,26,27) Then 6

                 When D_Cal_Week In (28,29,30,31) Then 7

                 When D_Cal_Week In (32,33,34,35) Then 8

                 When D_Cal_Week In (36,37,38,39,40) Then 9

                 When D_Cal_Week In (41,42,43,44) Then 10

                 When D_Cal_Week In (45,46,47,48) Then 11

                 When D_Cal_Week In (49,50,51,52,53) Then 12

                 END)

     
               ELSE /*WHEN D_CAL_WEEKS = 52 THEN*/

                 (Case When D_Cal_Week In (1,2,3,4) Then 1

                 When D_Cal_Week In (5,6,7,8) Then 2

                 When D_Cal_Week In (9,10,11,12,13) Then 3

                 When D_Cal_Week In (14,15,16,17) Then 4

                 When D_Cal_Week In (18,19,20,21) Then 5

                 When D_Cal_Week In (22,23,24,25,26) Then 6

                 When D_Cal_Week In (27,28,29,30) Then 7

                 When D_Cal_Week In (31,32,33,34) Then 8

                 When D_Cal_Week In (35,36,37,38,39) Then 9

                 When D_Cal_Week In (40,41,42,43) Then 10

                 When D_Cal_Week In (44,45,46,47) Then 11

                 When D_Cal_Week In (48,49,50,51,52) Then 12

                 END)

               END)

               WHERE D_PERIOD = 0;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql  UPDATE Qtemp/Date_Dim_calendar

               SET D_QOY = (Case When D_PERIOD >= 10 Then 4

                 When D_PERIOD >= 7 Then 3

                 When D_PERIOD >= 4 Then 2

                 Else 1

                 End)

               Where D_QOY = 0;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql MERGE INTO Qtemp/Date_Dim_calendar T1

                 USING (

                   Select D_CAL_WEEK_SEQ

                 , Row_number()

                   Over(Partition By D_CAL_YEAR, D_QOY

                        Order By D_CAL_WEEK_SEQ)

                 , Dense_rank() Over(Order By D_CAL_YEAR,D_QOY )

                 , Dense_rank() Over(Order By D_CAL_YEAR,D_PERIOD )

                 from Qtemp/Date_Dim_calendar

                 ) T2 (D_CAL_WEEK_SEQ, D_QTRWEEK, D_QUARTER_SEQ, D_PERIOD_SEQ)

                 ON T1.D_CAL_WEEK_SEQ=T2.D_CAL_WEEK_SEQ

                 WHEN MATCHED THEN

                 UPDATE SET T1.D_QTRWEEK = T2.D_QTRWEEK,

                   T1.D_QUARTER_SEQ= T2.D_QUARTER_SEQ,

                   T1.D_PERIOD_SEQ=T2.D_PERIOD_SEQ;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Insert Into DATE_DIM (  D_DATE_SK

               ,D_DATE

               ,D_MONTH_SEQ

               ,D_WEEK_SEQ

               ,D_QUARTER_SEQ

               ,D_PERIOD_SEQ

               ,D_YEAR

               ,D_DOW

               ,D_DAYOFWEEKINMONTH

               ,D_MOY

               ,D_DOM

               ,D_WOY

               ,D_WEEKYEAR

     
               ,D_PERIOD

               ,D_QOY

               ,D_QTRWEEK

               ,D_FISCAL_YEAR

               ,D_FISCAL_QUARTER

               ,D_FISCAL_PERIOD

     
               ,D_FIRST_DOM

               ,D_LAST_DOM

               ,D_FIRST_DOW

               ,D_LAST_DOW

     
               ,D_SAME_DAY_LY

               ,D_SAME_DAY_LQ

               ,D_SAME_WEEK_LY

     
               ,D_DAY_NAME

               ,D_MONTH_NAME

               ,D_MONTH_ABRV

               ,D_MONTH_YEAR

               ,D_QUARTER_NAME

               ,D_FISCAL_QUARTER_NAME

     
           //    ,D_HOLIDAY

           //    ,D_FOLLOWING_HOLIDAY

               ,D_WEEKEND

               ,D_MONTH_FIRST_SAT

               ,D_MONTH_FIRST_FULL_WEEK

               ,D_MONTH_LAST_SAT

               ,D_MONTH_LAST_FULL_WEEK

           //    ,D_CURRENT_13_WEEKS

               ,D_DATEYYMD

               ,D_DATEMDYY

               ,D_DATECYMD

               ,D_JULIAN

               ,D_CENTURY

               ,D_Year2

               )

     
               SELECT T1.D_DATE_SK

               , T1.D_DATE

               , T1.D_MONTH_SEQ

               , T1.D_WEEK_SEQ

               , T3.D_QUARTER_SEQ

               , T3.D_PERIOD_SEQ

               , YEAR(T1.D_DATE) D_YEAR

               , DAYOFWEEK(T1.D_DATE) D_DOW

               , (FLOOR(DAY(T1.D_DATE)/7)

                  + CASE WHEN MOD(DAY(T1.D_DATE),7) > 0 THEN 1 ELSE 0 END)

                    D_DAYOFWEEKINMONTH

               , MONTH(T1.D_DATE) D_MOY

               , DAY(T1.D_DATE) D_DOM

               , T3.D_Cal_Week D_WOY

               , T3.D_cal_year D_WEEKYEAR

               , T3.D_PERIOD

               , T3.D_QOY

               , T3.D_QTRWEEK

               , T2.D_FISCAL_YEAR

               , (MOD( T3.D_QOY +2, 4) +1  ) D_FISCAL_QUARTER

               , (MOD( T3.D_PERIOD + 8, 12) +1) D_FISCAL_PERIOD

               , (T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE) +1) D_FIRST_DOM

               , (T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE)

                  + DAYOFMONTH(LAST_DAY(T1.D_DATE))) D_LAST_DOM

               , (T1.D_DATE_SK- DAYOFWEEK(T1.D_DATE) +1) D_FIRST_DOW

               , (T1.D_DATE_SK- DAYOFWEEK(T1.D_DATE) +7) D_LAST_DOW

     
               , (T1.D_DATE_SK- ((CASE WHEN T3.D_cal_year = 1900 THEN NULL

                                       WHEN T3.D_cal_year = 1994 then 53

                                       ELSE 52 END)*7)) D_SAME_DAY_LY

     
               , (T1.D_DATE_SK -(CASE WHEN T3.D_cal_year = 1900 THEN NULL

                                       Else 91 End)) D_SAME_DAY_LQ

     
               , (T3.D_CAL_WEEK_SEQ -(CASE WHEN T3.D_cal_year = 1900 Then NULL

                                           WHEN T3.D_cal_year = 1994 Then 53

                                           ELSE 52 END)) D_SAME_WEEK_LY

     
               , CAST(DAYNAME(T1.D_DATE) AS CHAR(9)) D_DAY_NAME

     
               , MONTHNAME(T1.D_DATE) D_MONTH_NAME

     
               , UCASE(Substr(Monthname(T1.D_DATE), 1,3)) D_MONTH_ABRV

     
               , UCASE(Substr(Monthname(T1.D_DATE), 1,3)) ||' '|| YEAR(T1.D_DATE)

                  D_MONTH_YEAR

     
               , ('Q' || DIGITS(DEC(T3.D_QOY,1,0)) || ' '|| T3.D_CAL_YEAR )

                 D_QUARTER_NAME

     
               , ('Q' || DIGITS(DEC((MOD( T3.D_QOY +2, 4) +1  ),1,0))

                      || ' '|| T2.D_FISCAL_YEAR ) D_FISCAL_QUARTER_NAME

     
           //    , NULLIF(0,0) AS D_HOLIDAY

           //    , NULLIF(0,0) AS D_FOLLOWING_HOLIDAY

               , CASE WHEN DAYOFWEEK(T1.D_DATE) IN (1,7) THEN 'Y' END D_WEEKEND

               , CASE WHEN DAY(T1.D_DATE) < 8

                 AND DAYOFWEEK(T1.D_DATE) = 7

                 THEN 'Y' END D_MONTH_FIRST_SAT

               , (

           //* IF the first day of the month is dow 1 then week 1, else week 2 */

                 CASE WHEN DAYOFWEEK(DATE(T1.D_DATE_SK-DAYOFMONTH(T1.D_DATE)+1))= 1

                    AND WEEK(DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE)+1))

                    =WEEK(T1.D_DATE)

                  THEN 'Y'

                  WHEN DAYOFWEEK(DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE) +1)) > 1

                    AND (WEEK(DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE) +1))+1)

                    =WEEK(T1.D_DATE) THEN 'Y'

                  END) D_MONTH_FIRST_FULL_WEEK

               , (CASE WHEN DAY(T1.D_DATE) > DAYOFMONTH(LAST_DAY(T1.D_DATE))-7

                    AND DAYOFWEEK(T1.D_DATE) = 7

                  THEN 'Y'

                  END) D_MONTH_LAST_SAT

               , (

           //* IF the LAST day of the month is dow 7 then LAST week, else LAST WEEK-1 */

                 CASE WHEN DAYOFWEEK( DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE)

                   + DAYOFMONTH(LAST_DAY(T1.D_DATE))))=7

                   AND WEEK(DATE(T1.D_DATE_SK -DAYOFMONTH(T1.D_DATE)

                   + DAYOFMONTH(LAST_DAY(T1.D_DATE))))=WEEK(T1.D_DATE)

                 THEN 'Y'

                 WHEN DAYOFWEEK(DATE(T1.D_DATE_SK- DAYOFMONTH(T1.D_DATE)

                   + DAYOFMONTH(LAST_DAY(T1.D_DATE)))) < 7

                   AND (WEEK(DATE(T1.D_DATE_SK - DAYOFMONTH(T1.D_DATE)

                   + DAYOFMONTH(LAST_DAY(T1.D_DATE))))-1)=WEEK(T1.D_DATE)

                 THEN 'Y'

                 END) D_MONTH_LAST_FULL_WEEK

           //    , NULLIF(0,0) AS D_CURRENT_DAY

           //    , NULLIF(0,0) AS D_CURRENT_WEEK

           //    , NULLIF(0,0) AS D_CURRENT_MONTH

           //    , NULLIF(0,0) AS D_CURRENT_QUARTER

           //    , NULLIF(0,0) AS D_CURRENT_YEAR

           //    , NULLIF(0,0) AS D_CURRENT_13_WEEKS

     
               ,(YEAR(T1.D_DATE)*10000

                 +DEC(MONTH(T1.D_DATE),2,0)*100

                 +DEC(DAY(T1.D_DATE),2,0)) D_DATEYYMD

     
               , (DEC(MONTH(T1.D_DATE),2,0)*1000000

                 +DEC(DAY(T1.D_DATE),2,0)*10000

                 +YEAR(T1.D_DATE)) D_DATEMDYY

     
               ,((YEAR(T1.D_DATE)-1900)*10000

                 +DEC(MONTH(T1.D_DATE),2,0)*100

                 +DEC(DAY(T1.D_DATE),2,0)) D_DATECYMD

     
               , (DEC( YEAR(T1.D_DATE) *1000 + DAYOFYEAR(T1.D_DATE) ,7,0))  D_JULIAN

               , DEC(SUBSTR(DIGITS(DEC(YEAR(T1.D_DATE),4,0)),1,2),2,0) D_CENTURY

               , DEC(SUBSTR(DIGITS(DEC(YEAR(T1.D_DATE),4,0)),3,2),2,0) D_Year2

     
                 From Qtemp/Date_Dim_dates T1

                 Left join Qtemp/Date_Dim_fiscal T2

                 On T1.D_date_sk Between T2.D_FISCAL_BEGIN And T2.D_FISCAL_END

                 Join Qtemp/Date_Dim_calendar T3

                 On T1.D_date_sk Between T3.D_CAL_BEGIN And T3.D_CAL_END

             ;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
           Endsr;

           //---------------------------------------------------------------------

           // Set Current 13 weeks as of the last movement week

           //---------------------------------------------------------------------

           Begsr SetCurrent13Weeks;

     
             Exec Sql Merge Into Date_Dim T1

               Using (

                 Select T1A.D_date_SK

                 ,(CASE WHEN (

                     (Select TX.D_Week_Seq

                     From Date_dim TX

                     Join STCLPRPF S1

                     On TX.D_WOY= S1.SLWEEK AND MOD(TX.D_WEEKYEAR,100) = S1.SLYEAR

                     Order By TX.d_date_sk Desc Fetch First 1 Rows Only)

                   Between T1A.D_Week_Seq and T1A.D_Week_Seq+12)

                   Then 'Y' Else Null End )

                From Date_Dim t1A

               ) T2 (D_DATE_SK, D_CURRENT_13_WEEKS)

               On T1.D_DATE_SK=T2.D_DATE_SK

               When MATCHED

               And ( T1.D_CURRENT_13_WEEKS IS NULL

                 AND T2.D_CURRENT_13_WEEKS IS NOT NULL

                 OR  T1.D_CURRENT_13_WEEKS IS NOT NULL

                 AND T1.D_CURRENT_13_WEEKS <> T2.D_CURRENT_13_WEEKS

               )

               THEN UPDATE SET T1.D_CURRENT_13_WEEKS = T2.D_CURRENT_13_WEEKS

               ;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
           Endsr;

           //---------------------------------------------------------------------

           // Add US Holidays

           //---------------------------------------------------------------------

           Begsr AddHolidays;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME ='Thanksgiving Day'

             Where D_MOY = 11

             And D_DAYOFWEEKINMONTH = 4

             And D_DOW = 5;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME ='Christmas Day'

             Where D_MOY = 12

             And D_DOM = 25;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME ='Independance Day'

             Where D_MOY = 7

             And D_DOM = 4;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'New Year''s Day'

             Where D_MOY = 1

             And D_DOM = 1;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Memorial Day'

             Where D_Date_Sk In (

               Select Max(D_Date_Sk)

               From Date_Dim

               Where D_MOY = 5 And D_DOW = 2

               Group By D_Year, D_Moy);

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             //*Labor Day - First Monday in September*/

             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Labor Day'

             Where D_Date_Sk In (

               Select Max(D_Date_Sk)

               From Date_Dim

               Where D_MOY = 9 And D_DOW = 2

               Group By D_Year, D_Moy);

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Valentine''s Day'

             Where D_MOY = 2 And D_DOM = 14;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Saint Patrick''s Day'

             Where D_MOY = 3 And D_DOM = 17;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Martin Luthor King Jr Day'

             Where D_MOY = 1 And D_DOW = 2 And D_Year >= 1983

             And D_DAYOFWEEKINMONTH = 3;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'President''s Day'

             Where D_MOY = 2 And D_DOW = 2

             And D_DAYOFWEEKINMONTH = 3;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Mother''s Day'

             Where D_MOY = 5 And D_DOW = 1

             And D_DAYOFWEEKINMONTH = 2;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Father''s Day'

             Where D_MOY = 6 And D_DOW = 1

             And D_DAYOFWEEKINMONTH = 3;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Halloween'

             Where D_MOY = 10 And D_DOM = 31;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             //*Election Day - The first Tuesday after the first Monday in November*/

             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Election Day'    

             Where D_Date_Sk In (

               Select Max(D_Date_Sk)+1

               From Date_Dim

               Where D_MOY = 11 And D_DOW = 2

               And D_DAYOFWEEKINMONTH = 1

               Group By D_Year, D_Moy);

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'April Fools'' Day'

             Where D_MOY = 4 And D_DOM = 1;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
     
             // Easter is fun

             // These are the dates copied from this webpage.

             // http://www.timeanddate.com/holidays/us/easter-sunday

             Exec Sql Update Date_Dim

               Set D_HOLIDAY_NAME = 'Easter'

             Where D_Date In

              ('1900-04-15' ,'1901-04-07' ,'1902-03-30' ,'1903-04-12'

               ,'1904-04-03' ,'1905-04-23' ,'1906-04-15' ,'1907-03-31'

               ,'1908-04-19' ,'1909-04-11' ,'1910-03-27' ,'1911-04-16'

               ,'1912-04-07' ,'1913-03-23' ,'1914-04-12' ,'1915-04-04'

               ,'1916-04-23' ,'1917-04-08' ,'1918-03-31' ,'1919-04-20'

               ,'1920-04-04' ,'1921-03-27' ,'1922-04-16' ,'1923-04-01'

               ,'1924-04-20' ,'1925-04-12' ,'1926-04-04' ,'1927-04-17'

               ,'1928-04-08' ,'1929-03-31' ,'1930-04-20' ,'1931-04-05'

               ,'1932-03-27' ,'1933-04-16' ,'1934-04-01' ,'1935-04-21'

               ,'1936-04-12' ,'1937-03-28' ,'1938-04-17' ,'1939-04-09'

               ,'1940-03-24' ,'1941-04-13' ,'1942-04-05' ,'1943-04-25'

               ,'1944-04-09' ,'1945-04-01' ,'1946-04-21' ,'1947-04-06'

               ,'1948-03-28' ,'1949-04-17' ,'1950-04-09' ,'1951-03-25'

               ,'1952-04-13' ,'1953-04-05' ,'1954-04-18' ,'1955-04-10'

               ,'1956-04-01' ,'1957-04-21' ,'1958-04-06' ,'1959-03-29'

               ,'1960-04-17' ,'1961-04-02' ,'1962-04-22' ,'1963-04-14'

               ,'1964-03-29' ,'1965-04-18' ,'1966-04-10' ,'1967-03-26'

               ,'1968-04-14' ,'1969-04-06' ,'1970-03-29' ,'1971-04-11'

               ,'1972-04-02' ,'1973-04-22' ,'1974-04-14' ,'1975-03-30'

               ,'1976-04-18' ,'1977-04-10' ,'1978-03-26' ,'1979-04-15'

               ,'1980-04-06' ,'1981-04-19' ,'1982-04-11' ,'1983-04-03'

               ,'1984-04-22' ,'1985-04-07' ,'1986-03-30' ,'1987-04-19'

               ,'1988-04-03' ,'1989-03-26' ,'1990-04-15' ,'1991-03-31'

               ,'1992-04-19' ,'1993-04-11' ,'1994-04-03' ,'1995-04-16'

               ,'1996-04-07' ,'1997-03-30' ,'1998-04-12' ,'1999-04-04'

               ,'2000-04-23' ,'2001-04-15' ,'2002-03-31' ,'2003-04-20'

               ,'2004-04-11' ,'2005-03-27' ,'2006-04-16' ,'2007-04-08'

               ,'2008-03-23' ,'2009-04-12' ,'2010-04-04' ,'2011-04-24'

               ,'2012-04-08' ,'2013-03-31' ,'2014-04-20' ,'2015-04-05'

               ,'2016-03-27' ,'2017-04-16' ,'2018-04-01' ,'2019-04-21'

               ,'2020-04-12' ,'2021-04-04' ,'2022-04-17' ,'2023-04-09'

               ,'2024-03-31' ,'2025-04-20' ,'2026-04-05' ,'2027-03-28'

               ,'2028-04-16' ,'2029-04-01' ,'2030-04-21' ,'2031-04-13'

               ,'2032-03-28' ,'2033-04-17' ,'2034-04-09' ,'2035-03-25'

               ,'2036-04-13' ,'2037-04-05' ,'2038-04-25' ,'2039-04-10'

               ,'2040-04-01' ,'2041-04-21' ,'2042-04-06' ,'2043-03-29'

               ,'2044-04-17' ,'2045-04-09' ,'2046-03-25' ,'2047-04-14'

               ,'2048-04-05' ,'2049-04-18' ,'2050-04-10' ,'2051-04-02'

               ,'2052-04-21' ,'2053-04-06' ,'2054-03-29' ,'2055-04-18'

               ,'2056-04-02' ,'2057-04-22' ,'2058-04-14' ,'2059-03-30'

               ,'2060-04-18' ,'2061-04-10' ,'2062-03-26' ,'2063-04-15'

               ,'2064-04-06' ,'2065-03-29' ,'2066-04-11' ,'2067-04-03'

               ,'2068-04-22' ,'2069-04-14' ,'2070-03-30' ,'2071-04-19'

               ,'2072-04-10' ,'2073-03-26' ,'2074-04-15' ,'2075-04-07'

               ,'2076-04-19' ,'2077-04-11' ,'2078-04-03' ,'2079-04-23'

               ,'2080-04-07' ,'2081-03-30' ,'2082-04-19' ,'2083-04-04'

               ,'2084-03-26' ,'2085-04-15' ,'2086-03-31' ,'2087-04-20'

               ,'2088-04-11' ,'2089-04-03' ,'2090-04-16' ,'2091-04-08'

               ,'2092-03-30' ,'2093-04-12' ,'2094-04-04' ,'2095-04-24'

               ,'2096-04-15' ,'2097-03-31' ,'2098-04-20' ,'2099-04-12')

             ;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
             Exec Sql Update Date_DIm

                 Set D_HOLIDAY = CASE

                   WHEN D_Holiday_Name is null then null

                   Else 'Y' End;

             If SqlCode <> 0;

               EXSR ERRORSQL;

             Endif;

     
           ENDSR;

           //---------------------------------------------------------------------

           // Initialization routine

           //---------------------------------------------------------------------

           BEGSR *INZSR;

     
           //////  UTL10ARG( UTL10A.NAME );

           //////  UTL25ACL( PROCNAME :'*LIBL ' :'*PGM ' :Utl25ac.Text :UTL25AC.SIZE );

     
           ENDSR;

           //---------------------------------------------------------------------

           // Report header

           //---------------------------------------------------------------------

         OPRINT     EF           PRTHDR         1 03

         O****                       UTL10A.NAME         40

         O***                       JOBUSR              91

         O***                       PGMNAM             101

         O                       *DATE              111 '  /  /    '

         O***                       PGMTIM             121 '  .  .  '

         O                                          127 'Page'

         O                       PAGE          Z    132

     
         O          EF           PRTHDR         2

         O****                       UTL25AC.TEXT        50

     
         O          EF           PRTHDR         0

     
         O          EF           PRTHDR         2

     
          // Report Detail

         O          EF           PRTDTL         1

     
          // SQL Error

         O          EF           ERRORSQLP      1

         O***                       MSGTOOLS.DATA      132
generated in 0.008s
valid xhtml & css