ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Business days calculation

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Business days calculation

    Has anyone come up with a easy way to calculate the number of business days between 2 dates?

  • #2
    Since every country (even state) has different public holidays it is hard to come up with a definitive solution.

    Best i have seen so far is the idea of using date tables. IBM published the SQL to create these a while back as an appendix to the Web Query Redbook. Because it all works off a table you can adjust the data for your own company. We wrote about it here: http://archive.ibmsystemsmag.com/blo...-get-educated/ and in a bit more detail here: http://archive.ibmsystemsmag.com/ibm...l_discoveries/

    If you have trouble finding the SQL to set the table up then just email me and I'll try to dig out a copy.

    Comment


    • #3
      If you only want to count the days from monday to friday (without official vacation days ... which is difficult because in different countries, vacation days which depend on the easter date or for examples in Germany you even may have different vacation days between the states), you can use the following SQL Statement (just replace the start date '2019-01-01' and end date '2019-12-31' with the dates you want:

      Code:
      With Calendar(rundate)                                           
            as (   Values(Date('2019-01-01'))                      
                Union All                                 
                   Select Rundate + 1 Day  From Calendar    
                      where Rundate < '2019-12-31')             
       Select Count(rundate) "Total Work Days"                                     
          From Calendar                                      
          Where  DayOfWeek_ISO(Rundate) < 6;
      Birgitta

      Comment


      • #4
        We have a table keyed on a date column that contains columns that indicate (0 or 1) whether that date is a production day for each of our locations. The program that loads that table every year assumes Mon-Fri are business days, and the code contains arrays for holidays for each location. Those arrays have to be updated every year.

        The calculation is a simple SQL query summing the production day column for the desired location.

        Comment


        • #5
          I have using the DATE_CONV table supplied by IBM with DB2 WebQuery (see Jon's articles above)... You have to customize the table values for your company's holidays, but it works very well for us. The dates run well into the future (long after I'll care).

          I wrapped them in a service pgm in RPG to handle our nuances (i left those procedures out). I can probably locate the SQL statements I used to update the DATE_CONV table if you like (we only have 10 holidays, so you can't poke fun).

          Code:
                 dcl-proc  tbf_NextBusinessDay    Export;
          
                   dcl-pi tbf_NextBusinessDay    date;
                      inDate                     date const;
                   end-pi;
          
                   dcl-s  NextDay                date;
          
                     Exec Sql
          
                     select dc_date
                       into :NextDay
                       from asthhobj.date_conv
                       where dc_date > :inDate
                        and dc_weekend = 'N'
                        and dc_holiday = 'N'
                     order by dc_date
                     fetch first row only;
          
                     Return NextDay;
          
                 end-proc;
          and

          Code:
          
                 // =======================================================================
                 // Return Nth Business Day based on DATE_CONV table
                 //     In:  Date
                 //          Number of Days
                 //     OUT: Date
                 // =======================================================================
          
                 dcl-proc  tbf_NthBusinessDay    Export;
          
                   dcl-pi tbf_NthBusinessDay     date;
                      inDate                     date const;
                      inDays                     int(10) const;
                   end-pi;
          
                   dcl-s  ouDate                 date;
          
                     Exec Sql
                     With NthDays as (
                     Select dc_date, row_number() over(order by dc_date) as rownum
                       From date_conv
                       Where dc_date > :inDate
                        and dc_weekend = 'N'
                        and dc_holiday = 'N'
                     Order By dc_date
                     )
                     Select dc_date
                       Into :ouDate
                       From NthDays
                       Where rownum = :inDays;
          
                     return ouDate;
          
                 end-proc;
          
          
                 // =======================================================================
                 // Return Business Days between two dates based on DATE_CONV table
                 //     In:  FromDate
                 //          ToDate
                 //     OUT: Days
                 // =======================================================================
          
                 dcl-proc  tbf_BusinessDaysElapsed   Export;
          
                   dcl-pi *n                         int(10);
                      inFromDate                     date const;
                      inToDate                       date const;
                   end-pi;
          
                   dcl-s  Days                   int(10);
          
                     Days = 0;
          
                     Exec Sql
                     Select count(dc_date) into :Days
                     from date_conv
                     where dc_date >=:inFromDate and dc_date<:inToDate
                        and dc_weekend = 'N'
                        and dc_holiday = 'N';
          
                     Return Days;
          
                 end-proc;

          Comment

          Working...
          X