When work gets done during business days, that’s what this blog post is about. Moreover, when also the amount of business days is known upfront.
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…
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?
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.
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.
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;