We have many questions for reports to start on the number X business day of a month

This keeps me doing rather complex startcoding and now I thought it would be more easy to have a small table that holds the business days of the actual month, sequential numbered.

So what i need is a small process that steps trough all the days of a month, and only writes them to a file when it is a business day.
Two controls are needed
1) is it weekend, then skip
2) is it a holiday from the holiday calendar, then skip it to.

The result for the actual month should be this

20101001	1
20101004	2
20101005	3
20101006	4
20101007	5
20101008	6
20101011	7
20101012	8
20101013	9
20101014	10
20101015	11
20101018	12
20101019	13
20101020	14
20101021	15
20101022	16
20101025	17
20101026	18
20101027	19
20101028	20
20101029	21

The program should run on the first day of the month
Now when this exists, I can use it in Reportcaster or ETL to test if the actual day is the 3e or 6e working day, if not the program does not run, else it should run.

How do I create the loop in this case.

This message has been edited. Last edited by: FrankDutch,

Frank, What's the code used to create "The result for the actual month should be this"?

well it starts with something like

-SET &DATE=&YYMD;
-SET &LASTDAY=DATEMOV(&YYMD,'EOM');
-SET &NEXT=1;
-SET &NUMBERS=EDIT(&LASTDAY,'$$$$$$99');
-* test if this is weekend
....
-* test if this is holiday 
-WRITE &NEXT &DATE
-* something to get the next day and loop this &NUMBERS

If I knew how to do it, I had no reason to post it here...

thanks in advance

Let TABLE/DEFINE/COMPUTE handle the logic.

Generate the list of dates;
create a list of holidays and load as a Focus file, with IS_HOL/A1="H";
join the first to the second on the date field;
then, in define (or compute):

  DOW/A3 = suitably defined ;
  BUS_DAY_IND/I2= NOT ( ( DOW EQ 'SAT' OR 'SUN' ) OR (IS_HOL EQ 'H') );
  BUS_CNTR/I2=
    IF (MONTH EQ LAST MONTH)
        THEN LAST BUS_CNTR + BUS_DAY_IND
        ELSE                 BUS_DAY_IND;
  BUS_DAY/I2= BUS_DAY_IND * BUS_CNTR;

BUS_DAY_IND will be 0 or 1 to indicate Business days
BUS_CNTR gives the cumulative number of business days, month-to-date.
BUS_DAY will be sequence number (starting with 1) if date is a business day, else 0.

Here's a loop process for a start. This will not work for holidays unless you have a holiday file defined.

-*
-SET &CUR_YYMD = &YYMD;
-*
-SET &BEG_DT = DATECVT(DATEMOV(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'BOM'),'YYMD', 'I8YYMD');
-SET &END_DT = DATECVT(DATEMOV(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD');
-*
-TYPE --------------------------
-TYPE YYMD ------- &YYMD
-TYPE BEG_DT ----- &BEG_DT
-TYPE END_DT ----- &END_DT
-TYPE --------------------------
-*
-TYPE ******* BEGIN LOOP *******
-SET &THE_DT = &BEG_DT;
-SET &CNT_DT = 0;
-:LOOPER
-SET &LBD_DT = DATECVT(DATEADD(DATECVT(&THE_DT,'I8YYMD','YYMD'),'BD',0),'YYMD','I8YYMD');
-IF &THE_DT NE &LBD_DT GOTO :SKP;
-SET &CNT_DT = &CNT_DT + 1;
-TYPE &THE_DT &CNT_DT
-:SKP
-SET &THE_DT = AYMD(&THE_DT, 1, 'I8YYMD');
-IF &THE_DT GT &END_DT GOTO :EOF;
-GOTO :LOOPER
-:EOF
-TYPE ******* END LOOP *******
-RUN

Maybe this will help.
Jim

I have a DB2 table I could export and send you that holds that kind of information, if you like. It has years 1900-2031 and has colums for Weekend Date and Holiday flag

Something like this might work. Function DATEMOV will move to the next business day (NBD) based on your BUSDAYS setting, as well as the contents of your Holiday file. You could add code to create the master description, too, or manually create a permenant one.

FILEDEF WORKDATE DISK WORKDATE.FTM
-RUN
-*
-SET &CURYRMTH = EDIT(&YYMD,'999999$$');
-SET &BEGMONTH = &CURYRMTH | '01';
-SET &THISDATE = AYMD(&BEGMONTH,-1,'I8YYMD');
-*
-REPEAT ENDREPEAT1 FOR &I FROM 1 TO 25
-SET &SMARTDATE  = DATECVT(&THISDATE,'I8YYMD','YYMD');
-SET &NEXTBUSDAY = DATEMOV(&SMARTDATE,'NBD');
-SET &THISDATE   = DATECVT(&NEXTBUSDAY,'YYMD','I8YYMD');
-SET &THISYRMTH  = EDIT(&THISDATE,'999999$$');
-IF (&THISYRMTH NE &CURYRMTH) GOTO EXITREPEAT1 ;
-*TYPE &THISDATE &I
-WRITE WORKDATE NOCLOSE &THISDATE &I
-ENDREPEAT1
-EXITREPEAT1
-RUN

thanks for all the suggestions

the final result is this

-SET &ECHO=ALL
-* our holiday calender
SET HDAY = KEMP
-* creating the master for this file
FILEDEF MAS DISK WORKDATE.MAS
-RUN
-WRITE MAS  FILENAME=WORKDATE      , SUFFIX=FIX     , $
-WRITE MAS  SEGMENT=DATE, SEGTYPE=S0, $
-WRITE MAS  FIELDNAME=WERKDAG, USAGE=YYMD, ACTUAL=A8, $
-WRITE MAS  FIELDNAME=NUMMER, USAGE=I3, ACTUAL=A3, $
-*
-* the loop to get the correct records
FILEDEF WORKDATE DISK WORKDATE.FTM
-RUN
-*
-SET &CURYRMTH = EDIT(&YYMD,'999999$$');
-SET &BEGMONTH = &CURYRMTH | '01';
-SET &THISDATE = AYMD(&BEGMONTH,-1,'I8YYMD');
-*
-REPEAT ENDREPEAT1 FOR &I FROM 1 TO 25
-SET &SMARTDATE  = DATECVT(&THISDATE,'I8YYMD','YYMD');
-SET &NEXTBUSDAY = DATEMOV(&SMARTDATE,'NBD');
-SET &THISDATE   = DATECVT(&NEXTBUSDAY,'YYMD','I8YYMD');
-SET &THISYRMTH  = EDIT(&THISDATE,'999999$$');
-IF (&THISYRMTH NE &CURYRMTH) GOTO EXITREPEAT1 ;
-*TYPE &THISDATE &I
-WRITE WORKDATE NOCLOSE &THISDATE &I
-ENDREPEAT1
-EXITREPEAT1
-RUN
-* since we want to keep it as a foc file in our basedir we added this small report
APP HOLD BASEAPP
SET HOLDLIST=PRINTONLY
TABLE FILE WORKDATE
SUM  NUMMER
BY WERKDAG
ON TABLE HOLD AS WERKDAG FORMAT FOCUS INDEX WERKDAG
END

Now we can run a small fex that tests if the actual day is the number X workday of the month

If it is you get 1 record else zero.

I like the Next Business Day technique.