Has anyone come up with a easy way to calculate the number of business days between 2 dates?
Announcement
Collapse
No announcement yet.
Business days calculation
Collapse
X
-
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.
- Likes 1
-
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;
Comment
-
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
-
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;
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
Comment