I am hoping that there is at least one other person who has found themselves at a client who, for whatever reason, either cannot or will not create / update a holiday file. But they do still want date differences calculated using Working Days. So here is the situation:

TABLE_1 has a lot of data, and returns 2-3K rows with a normal report query. Within that table are two fields. START_DATE/YYMD and END_DATE/YYMD.

Now we all know that I can eliminate the weekends by using "WD" in the DATEDIF function, and if I had a holiday file "BD" would do the job. As I said at the start, I do not have, nor am I likely to get a holiday file. However, what I do have is:


TABLE_2, small table, has Company 'Holidays' in 2 fields DATE/YYMD and DESC/A50.

For the life of me I cannot figure out how, in a clean and simple manner I can then leave behind for the FTE's to understand, to determine how many rows in TABLE_2 are within the date range defined by the START_DATE and END_DATE in Table 1.

I tried to create a holiday file from TABLE_2 but security is locked down so tightly here I cannot save it where the SET HDAY command can find it. I have no access to any configuration files either.

WebFOCUS 7.6.10 on VMS

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

Have you tried something like this:

SET HOLDLIST = PRINTONLY
-*
TABLE FILE TABLE_2
BY DATE
ON TABLE SAVE AS HDAYTEMP
END
-RUN
-*
SET HDAY = TEMP

Unfortunately, yes.

  
SET HOLDLIST = PRINTONLY
 TABLE FILE TEMP
 PRINT GREGORIAN_DATE
 ON TABLE SAVE AS HDAYTEMP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       18  LINES=     18
 ALPHANUMERIC RECORD NAMED  HDAYTEMP
 0 FIELDNAME                         ALIAS         FORMAT        LENGTH
 GREGORIAN_DATE                    E01           A8              8
 TOTAL                                                           8
 0 DCB USED WITH FILE HDAYTEMP IS DCB=(RECFM=FB,LRECL=00008,BLKSIZE=00160)
 -*
 SET HDAY = TEMP
 -EXIT
 (FOC1892) FILE NOT FOUND : HDAYTEMPERRORS  *

Suppose you did a conditional join between the two tables and then counted up the results and joined the counts back up to FILE_1?

Something like this perhaps:

JOIN LEFT_OUTER FILE FILE_1 AT START_DATE
  TO MULTIPLE FILE FILE_2 AT DATE
  WHERE DATE GE START_DATE;
  WHERE DATE LE END_DATE;
END
 

If security permits, you might be able to use a public holidays file. Those are typically used from agenda applications, such as iCal and Outlock too. For example: http://www.mozilla.org/project...lendar/holidays.html

I'm assuming you have sufficient privileges to create focus databases and that your focus system user can download files off the internet.
You may need to generate a calendar file to match it against as well.

Rob: Unfortunately this must be done on a record by record basis. The start and end dates will usually be different. So I could conceivably have 2K different date ranges in one report that I have to figure out how many holidays are in each range.

Wep. TABLE_2 has all the holidays, so creation of a holidays file is no problem. It is using it that is giving me the headache.

Robert,

I think this could help you.
I created, using the CAR file, a file with 2 dates, FRDATE, TODATE.
I also created a holiday file. You can probably do the same using the one on hand.
With these 2 I can calculate the number of holidays between FRDATE and TODATE which you can use to subtract from the WDays.

  
-* File rfbowley01.fex
DEFINE FILE CAR
FRDATE/YYMD=DCOST/100 + 100 * 366 - 50;
TODATE/YYMD=RCOST/100 + 100 * 366 - 50;
END
TABLE FILE CAR
PRINT  FRDATE TODATE
BY COUNTRY BY CAR BY MODEL BY BODY
ON TABLE HOLD AS RFB1 FORMAT FOCUS
END
-RUN
EX -LINES 6 EDAPUT MASTER,HLDAY,C,MEM
FILENAME=HLDAY, SUFFIX=FOC
 SEGNAME=LINK, SEGTYPE=S1
  FIELDNAME=BLANK, ALIAS=BLANK, FORMAT=A1, INDEX=I, $
 SEGNAME=HLDAY, SEGTYPE=S1, PARENT=LINK
  FIELDNAME=HLDATE, ALIAS=HLDATE, FORMAT=YYMD, $
-RUN
CREATE FILE HLDAY
-RUN
MODIFY FILE HLDAY
FREEFORM HLDATE
DATA
-REPEAT #L FOR &I FROM 1 TO 12;
-SET &J=IF &I GE 10 THEN &I ELSE '0'|&I; 
HLDATE='2001/&I/01',$
-#L
END
-RUN
JOIN BLANK WITH FRDATE IN RFB1 TO BLANK IN HLDAY AS A_
DEFINE FILE RFB1
BLANK/A1 WITH FRDATE=' ';
HLDAYCOUNT/I3=IF HLDATE GE FRDATE AND HLDATE LE TODATE THEN 1 ELSE 0;
END
TABLE FILE RFB1
SUM 
  FRDATE TODATE HLDAYCOUNT
BY COUNTRY BY CAR BY MODEL BY BODY
END

See if this helps.

I'm not sure if I'm misunderstanding, or if I'm not communicating well. . .

If we tabled the results after the conditional join, it would look something like this:

TABLE FILE FILE_1
PRINT 
   EMPLOYEE_ID START_DATE END_DATE DATE DESC
ON TABLE HOLD AS HOLIDAYS
END

     EMP_ID  START_DATE  END_DATE    DATE        DESC
     ------  ----------  --------    ----        ---- 
      12345  2008/07/19  2009/07/01  2009/01/01  New Years                     
      12345  2008/07/19  2009/07/01  2009/04/10  Good Friday                   
      12345  2008/07/19  2009/07/01  2009/05/25  Memorial Day                  
      22345  2009/07/01  2010/07/01  2009/07/03  Independence Day              
      22345  2009/07/01  2010/07/01  2009/09/07  Labor Day                     
      22345  2009/07/01  2010/07/01  2009/11/26  Thanksgiving                  
      22345  2009/07/01  2010/07/01  2009/12/25  Christmas                     
      22345  2009/07/01  2010/07/01  2010/01/01  New Years                     
      22345  2009/07/01  2010/07/01  2010/04/02  Good Friday                   
      22345  2009/07/01  2010/07/01  2010/05/31  Memorial Day                  

And then we could proceed like this:

TABLE FILE HOLIDAYS
SUM CNT.DATE AS 'HOLIDAY_CNT'
BY EMPLOYEE_ID
BY START_DATE
WHERE DATE NE ''
ON TABLE PCHOLD FORMAT WP
END

To get these results:

                           
  EMPLOYEE_ID  START_DATE  HOLIDAY_CNT
  -----------  ----------  -----------
        12345  2008/07/19            4
        22345  2009/07/01            7

It looks like you are on z/OS.

If so, it should be possible to create a application specific/departmental version of the Holiday File, and make it available to your programs. (Given what you are saying it sounds like you do not have the the option to create a site-wide Holiday File.)

Here is something you can try, however I am not able to test it because I am no longer on z/OS.

Create a new ERRORS partitioned dataset (PDS), e.g. 'DEPT.ERRORS.DATA'. (I'd use the same DCB as the production version.)

Add a member to the new PDS for your Holiday File, e.g., (HDAY2012).

Open the member in SPF Edit and type in your holdiays, e.g., 20120101 New Years Day.

In your application concatenate your departmental ERRORS file to the production version.

You could do this in the JCL or via DYANAM.

Something like:

DYNAM ALLOC FILE ERRORS DS DEPT.ERRORS.DATA SHR REUSE 
DYNAM ALLOC FILE ERRORS1 DS PROD.ERRORS.DATA SHR REUSE 
DYNAM CONCAT DDN ERRORS ERRORS1

Your code should work like normal, reading from the normal PROD version of the ERRORS file when/as needed. When the code sees the SET HDAY = 2012 command, it should pull from the departmental version of the ERROR file.

MathematicalRob, you are indeed a lifesaver. Here is the Final result that works perfectly

 
-**********************************************************************************************
-*	Get the Contents of the Holiday table on DB2 into a FOCUS table
-**********************************************************************************************

SQL DB2
SELECT * FROM FIWP.TBHOLIDAY
WHERE BCBS_FL = 'Y'
;

TABLE
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
PRINT HOLIDAY_DESC
BY HOLIDAY_DT
ON TABLE HOLD AS HDAYS FORMAT FOCUS INDEX HOLIDAY_DT
END
-RUN

-**********************************************************************************************
-*	Get the DATE values from H1 into a FOCUS table
-**********************************************************************************************

TABLE FILE H1
BY DATE
ON TABLE HOLD AS EFFDATES FORMAT FOCUS INDEX DATE
END
-RUN

JOIN LEFT_OUTER FILE EFFDATES AT DATE TAG T1 TO ALL
  				FILE HDAYS AT HOLIDAY_DT TAG T2 AS JW1
  WHERE HDAYS.HOLIDAY_DT GE EFFDATES.DATE;
  WHERE HDAYS.HOLIDAY_DT LE &YYMD.EVAL;
END

-**********************************************************************************************
-*	Count the hoildays and hold the results in a FOCUS table
-**********************************************************************************************
TABLE FILE EFFDATES
SUM CNT.HOLIDAY_DT AS 'HOLCNT'
BY DATE
ON TABLE HOLD AS HOLCOUNT FORMAT FOCUS INDEX DATE
END
-RUN

JOIN LEFT_OUTER DATE IN H1
	TO UNIQUE DATE IN HOLCOUNT
	AS J0
END

 

Great; I'm glad it worked out!

Rob