Business days calculation in RPGLE

45 pts.
Tags:
AS 400
RPGLE
Could you please some one help me, how to do the business Days calculation in RPGLE? For example I want to add the 6 business days to current date. I want to add the 6 business days to (1st March 2018) current date, it will be 9th March.

Answer Wiki

Thanks. We'll let you know when a new response is added.
here are a pair of SQL UDFs to do this:
CREATE FUNCTION qgpl/isHoliday (checkDate DATE)
RETURNS CHARACTER (1)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE returnVar CHARACTER (1);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
RETURN NULL;
SET returnVar = 'N';
IF DAYOFWEEK_ISO(checkDate) < 6 THEN

  /* New Year’s Day - Jan 1 or Mon Jan 2 or Fri Dec 31*/
  IF MONTH(checkDate) = 01 AND DAY(checkDate) = 01
     OR MONTH(checkDate) = 01 AND DAY(checkDate) = 02 AND
        DAYOFWEEK_ISO(checkDate) = 1
     OR MONTH(checkDate) = 12 AND DAY(checkDate) = 31 AND
        DAYOFWEEK_ISO(checkDate) = 5
    THEN SET returnVar = 'Y';

  /* Memorial Day - Last Mon in May */
  ELSEIF DAYOFWEEK_ISO(checkDate) = 1 AND MONTH(checkDate) = 05
     AND DAY(checkDate) BETWEEN 25 AND 31
    THEN SET returnVar = 'Y';

  /* Independence Day - Jul 4 or Mon Jul 5 or Fri Jul 3*/
  ELSEIF MONTH(checkDate) = 07 AND DAY(checkDate) = 04
     OR MONTH(checkDate) = 07 AND DAY(checkDate) = 05 AND
        DAYOFWEEK_ISO(checkDate) = 1
     OR MONTH(checkDate) = 07 AND DAY(checkDate) = 03 AND
        DAYOFWEEK_ISO(checkDate) = 5
    THEN SET returnVar = 'Y';

  /* Labor Day - First Mon in Sep */
  ELSEIF DAYOFWEEK_ISO(checkDate) = 1 AND MONTH(checkDate) = 09
     AND DAY(checkDate) BETWEEN 01 AND 07
    THEN SET returnVar = 'Y';

  /* Thanksgiving - Fourth Thu in Nov */
  ELSEIF DAYOFWEEK_ISO(checkDate) = 4 AND MONTH(checkDate) = 11
     AND DAY(checkDate) BETWEEN 22 AND 28
    THEN SET returnVar = 'Y';

  /* Christmas - Dec 25 or Mon Dec 26 or Fri Dec 24 */
  ELSEIF MONTH(checkDate) = 12 AND DAY(checkDate) = 25
     OR MONTH(checkDate) = 12 AND DAY(checkDate) = 26 AND
        DAYOFWEEK_ISO(checkDate) = 1
     OR MONTH(checkDate) = 12 AND DAY(checkDate) = 24 AND
        DAYOFWEEK_ISO(checkDate) = 5
    THEN SET returnVar = 'Y';
  END IF;

END IF;
RETURN returnVar;
END
 
CREATE FUNCTION qgpl/addWorkDays (addDate DATE, numDays INT)
RETURNS DATE
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
RETURN NULL;
WHILE numDays > 0 DO
  SET addDate = addDate + 1 DAY;
  
  /* if that is a Saturday, jump to Monday */
  IF DAYOFWEEK_ISO(addDate) = 6
    THEN SET addDate = addDate + 2 DAY;
  
  /* if that is a Sunday, jump to Monday */
  ELSEIF DAYOFWEEK_ISO(addDate) = 7
    THEN SET addDate = addDate + 1 DAY;
  END IF;
  
  /* if a Holiday, jump to next day */
  IF isHoliday(addDate) = 'Y'
    THEN SET addDate = addDate + 1 DAY;
  END IF;
  SET numDays = numDays - 1;
END WHILE;
RETURN addDate;
END

Discuss This Question: 19  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    First, a definition of "business days" is needed. Are there ever any days other than weekends that are not "business days"?
    25,125 pointsBadges:
    report
  • ToddN2000
    This is a tough calculation to do without using a shop calendar due to unknown factors like holidays and shutdowns. You might be better of using embedded SQL.  Something like this to determine the day of the week and adjust for weekends after you know. 
    EXEC SQL Set :DayOfWeekNum = DayOfWeek(:Checkdate) // Sunday is 1, Monday is 2, etc EXEC SQL Set :DayOfWeekNum = DayOfWeek_ISO(:Checkdate) // Monday is 1, Tuesday is 2, etc EXEC SQL Set :DayName = DAYNAME(:Checkdate); 
    104,305 pointsBadges:
    report
  • venkateshh
    TheRealRaven, There are only weekends holidays, could you please suggest me?
    45 pointsBadges:
    report
  • venkateshh
    ToddN2000, I'm not familiar with SQL coding, could you please suggest me same kind of coding in RPGIV or RPGLE. 
    45 pointsBadges:
    report
  • ToddN2000
    Unless you have a table with your company holidays defined it wont work. 

    you might try defining the dates like this 
    D Date            S             10D   DATFMT(*ISO)
    D DateOut         S             10D   DATFMT(*ISO)

    and calculate the new date something like this, you still would have to adjust foe weekends and holidays
    C     Date          ADDDUR    6:*D          DateOut

    Or something like this might get you started
    RPGLE Sample Code to get day of week for the Current Date or any given date
    
    d Day_Of_Week     s              1p 0                                  
    D My_date         S               D   inz(d'2011-03-15')               
    D My_date2        S              8s 0 inz(20110415)                    
                                                                           
     /free                                                                 
        // *Mon = 0 ... Sun = 6                                            
        Day_Of_Week = %rem(%diff(%date():d'0001-01-01':*days):7);          
                                                                           
        Day_Of_Week = %rem(%diff(My_date:d'0001-01-01':*days):7);          
                                                                           
        Day_Of_Week = %rem(%diff(%date(My_date2):d'0001-01-01':*days):7); 
                                                                          
    
        SELECT;                                                            
              WHEN Day_Of_Week = 0;                                        
                dsply '*MON';                                              
              WHEN Day_Of_Week = 1;                                        
                dsply '*TUE';                                              
              WHEN Day_Of_Week = 2;                                        
                dsply '*WED';                                              
              WHEN Day_Of_Week = 3;                                        
                dsply '*THU';                                              
              WHEN Day_Of_Week = 4;                                        
                dsply '*FRI';                                              
              WHEN Day_Of_Week = 5;             
                dsply '*SAT';                   
              WHEN Day_Of_Week = 6;             
                dsply '*SUN';                   
              OTHER;                            
        ENDSL;                                  
                                                
     /end-free 
    104,305 pointsBadges:
    report
  • azohawk
    I have to agree, without a shop calendar this can be difficult. I would create a shop calendar that includes every day of the year. It would include the date, day of the week, include/exclude fields. Have a procedure that is called when you need to know x business days out and the start date, return is the future date. Exclude holidays and weekends. The procedure would not count the excluded days. If a date needs to be changed on excluded/included, it is a simple matter to change the field for that date.
    3,505 pointsBadges:
    report
  • ToddN2000
    Azohawk's suggestion would take almost no time to set up. Create the file or do it in excel and upload it. It would need as little as 3 fields, date, day of week and Working or not. Then in your program you could SETLL with the current date and read this file with a do loop until you hit x number of working days. You would then have the new day / date for use.
    104,305 pointsBadges:
    report
  • WoodEngineer
    I have coded a lot of date calcs but not this one.  ToddN2000's suggestions makes the most sense for a simple routine.  If you only want to ignore weekends (Sat, Sun) and never have to worry about holidays you could do this without a date file something like this?  This routine is not perfect but you get the idea.

    Working date + 1 day
    Is day a Sat or Sun?
       If yes, loop back to top of routine.
    Add one to working date
    Have number of days been added?
    If Yes, exit
    else
    Loop back to top of routine.
    7,970 pointsBadges:
    report
  • azohawk
    WoodEngineer. I agree with that, but the question included taking into account holidays. Also, I have worked at companies that do an annual shutdown for a week or more, those dates would need to be excluded. Depending on why dates are being excluded, sometimes it may be appropriate to include the weekend dates. My current company works production on a 10x4 schedule. Last summer we went to an 11x5 schedule, should the 5th day be included? Just thinking through some of the practical issues.
    3,505 pointsBadges:
    report
  • GregManzo
    I did something like this back in the early 80's using a 'holiday calendar' - it worked well for the first year, but then nobody bothered to maintain the calendar.
    To do this _correctly_ you need a calendar so you won't include holidays in 'business days', but to have a calendar work properly you need to have made the business aware that they will need to maintain the calendar at intervals. Failing that you may as well just skip weekends and accept that it will be close most of the time, but not perfect.
    2,590 pointsBadges:
    report
  • ToddN2000
    The only real way to do it is using a calendar file. Companies and countries can have different holidays so there is no real easy way. Just set up a year or two in advance and make the calendar update part of your year end processing. There is no "one size fits all" calendar to cover everyone.
    104,305 pointsBadges:
    report
  • venkateshh
    Dear ToddN2000, could you please suggest me if I found the shop floor calendar for the company, what the logic to add the 6 Business days to current date? Regards, Venkateshh
    45 pointsBadges:
    report
  • venkateshh
    Thanks all,

    I got an idea to code my logic 
    45 pointsBadges:
    report
  • BigKat
    try these SQL UDFs:

    CREATE FUNCTION qgpl/isHoliday (checkDate DATE)

    RETURNS CHARACTER (1)

    LANGUAGE SQL

    DETERMINISTIC

    CONTAINS SQL

    BEGIN

    DECLARE returnVar CHARACTER (1);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    RETURN NULL;

    SET returnVar = 'N';

    IF DAYOFWEEK_ISO(checkDate) < 6 THEN

     

      /* New Year’s Day - Jan 1 or Mon Jan 2 or Fri Dec 31*/

      IF MONTH(checkDate) = 01 AND DAY(checkDate) = 01

         OR MONTH(checkDate) = 01 AND DAY(checkDate) = 02 AND

            DAYOFWEEK_ISO(checkDate) = 1

         OR MONTH(checkDate) = 12 AND DAY(checkDate) = 31 AND

            DAYOFWEEK_ISO(checkDate) = 5

        THEN SET returnVar = 'Y';

     

      /* Memorial Day - Last Mon in May */

      ELSEIF DAYOFWEEK_ISO(checkDate) = 1 AND MONTH(checkDate) = 05

         AND DAY(checkDate) BETWEEN 25 AND 31

        THEN SET returnVar = 'Y';

     

      /* Independence Day - Jul 4 or Mon Jul 5 or Fri Jul 3*/

      ELSEIF MONTH(checkDate) = 07 AND DAY(checkDate) = 04

         OR MONTH(checkDate) = 07 AND DAY(checkDate) = 05 AND

            DAYOFWEEK_ISO(checkDate) = 1

         OR MONTH(checkDate) = 07 AND DAY(checkDate) = 03 AND

            DAYOFWEEK_ISO(checkDate) = 5

        THEN SET returnVar = 'Y';

     

      /* Labor Day - First Mon in Sep */

      ELSEIF DAYOFWEEK_ISO(checkDate) = 1 AND MONTH(checkDate) = 09

         AND DAY(checkDate) BETWEEN 01 AND 07

        THEN SET returnVar = 'Y';

     

      /* Thanksgiving - Fourth Thu in Nov */

      ELSEIF DAYOFWEEK_ISO(checkDate) = 4 AND MONTH(checkDate) = 11

         AND DAY(checkDate) BETWEEN 22 AND 28

        THEN SET returnVar = 'Y';

     

      /* Christmas - Dec 25 or Mon Dec 26 or Fri Dec 24 */

      ELSEIF MONTH(checkDate) = 12 AND DAY(checkDate) = 25

         OR MONTH(checkDate) = 12 AND DAY(checkDate) = 26 AND

            DAYOFWEEK_ISO(checkDate) = 1

         OR MONTH(checkDate) = 12 AND DAY(checkDate) = 24 AND

            DAYOFWEEK_ISO(checkDate) = 5

        THEN SET returnVar = 'Y';

      END IF;

     

    END IF;

    RETURN returnVar;

    END


     

    CREATE FUNCTION qgpl/addWorkDays (addDate DATE, numDays INT)

    RETURNS DATE

    LANGUAGE SQL

    DETERMINISTIC

    CONTAINS SQL

    BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    RETURN NULL;

    WHILE numDays > 0 DO

      SET addDate = addDate + 1 DAY;

     

      /* if that is a Saturday, jump to Monday */

      IF DAYOFWEEK_ISO(addDate) = 6

        THEN SET addDate = addDate + 2 DAY;

     

      /* if that is a Sunday, jump to Monday */

      ELSEIF DAYOFWEEK_ISO(addDate) = 7

        THEN SET addDate = addDate + 1 DAY;

      END IF;

     

      /* if a Holiday, jump to next day */

      IF isHoliday(addDate) = 'Y'

        THEN SET addDate = addDate + 1 DAY;

      END IF;

      SET numDays = numDays - 1;

    END WHILE;

    RETURN addDate;

    END

    9,160 pointsBadges:
    report
  • GregManzo
    Nice idea, but hard-coding a bunch of holidays ain't going to work for most of the planet. I'm pretty sure Vendateshh is not an American 'cos he refers to dates by day/month/year, not month/day/year. Outside of the USA most countries don't do Thanksgiving, and 'Independence Day' is just a movie, not a holiday. It gets even worse though, in Australia we have a holiday for the Queen's Birthday (not her actual birthday), but this holiday is on a different day depending on which state you are in. To do this properly you need to have a calendar file, and it needs to be keyed by date AND geographic location. And it needs to be updated regularly. Or you just accept the fact that it will be wrong sometimes. :-(
    2,590 pointsBadges:
    report
  • ToddN2000
    Not sure if it's possible but you might be able to export the Outlook calendar to a file for use. I know there are settings in Outlook that flag the holidays. Just a though I may have to look into further.
    104,305 pointsBadges:
    report
  • BigKat
    The SQL code was a sample. I assumed the OP would change the isHoliday(date) function for their holidays.
    9,160 pointsBadges:
    report
  • BigKat
    The Outlook may not be a good idea either as it has all of those "holidays" like Sweetest Day, Boss's Day, Admin Professional's Day, etc.
    9,160 pointsBadges:
    report
  • ToddN2000
    @BigKat: Had to hunt a bit but found a link I used to remove a lot of those dumb holidays that show up in the Outlook calendar.

    104,305 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: