On business days

Given a date referred to as the date to start counting from, manually finding the date resulting from adding a fixed number of business days known beforehand to that start date is not too difficult, especially when aided by a carved-out in stone, nowadays printed, copy of a suitable calendar. And as long as the number of business days at hand is short enough to be manageable for a manual calculation…

Problem definition

Let’s now define the problem I’d like to solve more in terms used in database software development, particularly when using SQL. I’ll be working with a specific example to show you how to solve the problem, but I’m sure you’ll be able to generalize and translate the example into your own  specific needs.

Suppose we have a table PRODUCTS defining product attributes like NAME and a whole number (construction) DAYS  indicating how many business days are needed to produce the product.

I would like to build a view VW_PRODUCTS listing the product id, name, construction days, today’s date, and finally the calendar date the product would be in constructed state if we would start constructing right at the first coming business day after today.

Example data, with our yet to be build PREPARED column grayed out:

SQL> select * from vw_products order by id;

  ID NAME                 TODAY      DAYS PREPARED  
---- -------------------- ---------- ---- ----------
   1 Transistor Radio     2017-07-27    1 2017-07-28
   2 Dial Telephone       2017-07-27    2 2017-07-31
   3 Popcorn Maker        2017-07-27    5 2017-08-03
   4 G.I. Joe Figure      2017-07-27    9 2017-08-09
   5 Beer Cooler          2017-07-27   11 2017-08-11

Which expression would calculate the value for PREPARED, listing the expected end-of-construction date, based on the addition of the TODAY date and the DAYS number of business days?

Assumptions

We don’t make any assumptions on the global database NLS settings regarding NLS_LANGUAGE and / or NLS_TERRITORY, more on why I mention that further on. The TODAY date doesn’t necessarily has to be a business day itself.

Business days

We of course need to define business days, which will be as simple as this: any day of the week not being a Saturday or a Sunday is a business day.

How to recognize a business day in SQL

We could literally translate the business days definition above, like this to determine whether or not a date is to be counted as a business day:

select  case to_char(sysdate,'DAY')
          when 'SATURDAY' then 0
          when 'SUNDAY'   then 0
          else                 1
        end as business_day_ind
from    dual;

But that would give unexpected results if your session NLS settings would have been different from English. So this could be improved by supplying a value for the third, optional parameter of to_char to make sure the result of the function is in English:

select  case to_char(sysdate,'DY', 'nls_date_language=english')
          when 'SAT' then 0
          when 'SUN' then 0
          else            1
        end as business_day_ind
from    dual;

Showing that setting the NLS parameter also works for the abbreviated day format.

We can now already come up with our first implementation of the requested view:

create or replace view vw_products1 as
with function count_calendar_days(p_start_date date, p_days pls_integer)
return pls_integer
as
  l_calendar_days pls_integer;
begin
  select  min(calendar_days)
  into    l_calendar_days
  from    ( select  calendar_days
                    ,       sum(business_day_ind) 
                            over ( order by calendar_days ) nrof_bdays
                    from    ( select level calendar_days
                              ,      case to_char
                                     ( p_start_date+level
                                     ,'DY'
                                     , 'nls_date_language=english')
                                     when 'SAT' then 0
                                     when 'SUN' then 0
                                     else            1
                                     end as business_day_ind
                              from   dual
                              connect by level <= p_days * 2
                            )
          )  
  where   nrof_bdays = p_days;
  return l_calendar_days;
end count_calendar_days;
select  id
,       name
,       trunc(sysdate) today
,       days
,       trunc(sysdate)+count_calendar_days (trunc(sysdate), days) prepared
from    products
order by id;        

SQL> 
SQL> select * from vw_products1 order by id;

  ID NAME                 TODAY      DAYS PREPARED  
---- -------------------- ---------- ---- ----------
   1 Transistor Radio     2017-07-27    1 2017-07-28
   2 Dial Telephone       2017-07-27    2 2017-07-31
   3 Popcorn Maker        2017-07-27    5 2017-08-03
   4 G.I. Joe Figure      2017-07-27    9 2017-08-09
   5 Beer Cooler          2017-07-27   11 2017-08-11

The function in fact iteratively adds calendar days to the starting days, keeps track of the number of actual added business days, until the required number of business days is added. We know in advance that roughly at most two times the number of required business days is needed to be looped through, hence the “level <= p_days * 2” equation.

The prepared date is now easily derived from the addition of the starting date and the found number of calendar days, and we could be fine by that.

However, this method fails to be concise. Also, we should critique on the fact that each and every date between the start date and the sought for prepared date is being ‘probed’ for as to what day of the week it is. That’s too much information gathering. We in fact only need to know the day of the week for the starting date.

Back to the drawing board

Going back, isn’t there a date format returning the day of the week as a number, with a perspective of being able to perform calculations based on that number ? Well, in a way, there is:

alter session set nls_territory='the netherlands';
select  refdate
,       to_char(refdate,'DAY', 'nls_date_language=english') day
,       case to_char(refdate,'DY', 'nls_date_language=english')
          when 'MON' then 1
          when 'TUE' then 2
          when 'WED' then 3
          when 'THU' then 4
          when 'FRI' then 5
          when 'SAT' then 6
          when 'SUN' then 7
        end as d1
,       to_number(to_char(refdate,'D')) d2
,       trunc(refdate)-trunc(refdate,'IW')+1 d3
from    ( select  sysdate+level-1 as refdate 
          from    dual 
          connect by level <= 7
        );

REFDATE  DAY          D1   D2   D3
-------- ---------- ---- ---- ----
27-07-17 THURSDAY      4    4    4
28-07-17 FRIDAY        5    5    5
29-07-17 SATURDAY      6    6    6
30-07-17 SUNDAY        7    7    7
31-07-17 MONDAY        1    1    1
01-08-17 TUESDAY       2    2    2
02-08-17 WEDNESDAY     3    3    3

7 rows selected. 

The result of columns d1, d2 and d3 are the same, but that’s because we “cheated”: we had to make sure the territory settings weren’t set to for example ‘american’ … Unfortunately, we cannot set the territory using the same parameter of to_char we’ve used earlier to set the language. Now, credits to Frank Kulash, there’s once more an alternative to that making use of the iso week format:

select  trunc(sysdate)-trunc(sysdate,'IW')+1 as d
from    dual;

will return 1 for Monday up to 7 for Sunday, regardless of nls settings. We have already checked our results in column d3 above.

Where to start counting from

Notice that adding X business days to a date starting on a Tuesday, is the same as adding X+1 days to the Monday just before that. Why do I mention that ? Well, If you know your starting date is a Monday, the calculation is easy. Adding Y business days to a Monday would boil down to adding as many weeks to that Monday as the number of multiples of five fitting within Y, and after that adding the remainder. The first part can be expressed as TRUNC(Y/5), the second part as MOD(Y,5).

So first thing we need to determine is the OFFSET, which is the (possibly negative) amount of calendar days to add to the start date to get to the the day of the week that was “Monday”. It turns out that the expression “mod(12-d,7)-4”, with “d” being the day number as calculated above, calculates just that offset. For a Monday, it returns mod(12-1,7)-4=4-4=0, for a Friday a -4, for a Saturday a 2 returned, indicating correctly we’d not have to go back, but two calendar days forward to get to Monday.

Now we need to determine how many calendar days to add from that offset date (DAYS_FROM_OFFSET). That would be the inverse of the offset number calculated above, except for starting days being not a weekday… The expression “days+greatest(mod(d+4,5),0)” correctly calculates the amount of calendar days to be added if we start at the offset date mentioned above.

Final definition

So finally we can define our final VW_PRODUCTS view:

create or replace view vw_products as
select  id
,       name
,       today
,       days
,       today+offset+7*trunc(days_from_offset/5)+mod(days_from_offset,5) prepared
from    ( select  prd.*
          ,       mod(12-d,7)-4 offset
          ,       days+greatest(mod(d+4,5),0) days_from_offset
          from    ( select  prd.*
                    ,       trunc(sysdate) today
                    ,       trunc(sysdate)-trunc(sysdate,'IW')+1 d
                    from    products prd
                  ) prd
        ) prd       
order by id;