insight 
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…
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.rnrnSuppose we have a table PRODUCTS defining product attributes like NAME and a whole number (construction) DAYS indicating how many u003cemu003ebusiness daysu003c/emu003e are needed to produce the product.rnrnI 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.rnrnExample data, with our yet to be build PREPARED column grayed out:rnu003cpreu003eSQLu0026gt; select * from vw_products order by id;rnrn ID NAME TODAY DAYS PREPARED rnu002du002du002du002d u002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002d u002du002du002du002du002du002du002du002du002du002d u002du002du002du002d u002du002du002du002du002du002du002du002du002du002drn 1 Transistor Radio 2017-07-27 1 2017-07-28rn 2 Dial Telephone 2017-07-27 2 2017-07-31rn 3 Popcorn Maker 2017-07-27 5 2017-08-03rn 4 G.I. Joe Figure 2017-07-27 9 2017-08-09rn 5 Beer Cooler 2017-07-27 11 2017-08-11rnu003c/preu003ernWhich 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:rnu003cpreu003eselect case to_char(sysdate,’DAY’)rn when ‘SATURDAY’ then 0rn when ‘SUNDAY’ then 0rn else 1rn end as business_day_indrnfrom dual;rnu003c/preu003ernBut 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:rnu003cpreu003eselect case to_char(sysdate,’DY’, ‘nls_date_language=english’)rn when ‘SAT’ then 0rn when ‘SUN’ then 0rn else 1rn end as business_day_indrnfrom dual;rnu003c/preu003ernShowing that setting the NLS parameter also works for the u003cemu003eabbreviated dayu003c/emu003e format.rnrnWe can now already come up with our first implementation of the requested view:rnu003cpreu003ecreate or replace view vw_products1 asrnwith function count_calendar_days(p_start_date date, p_days pls_integer)rnreturn pls_integerrnasrn l_calendar_days pls_integer;rnbeginrn select min(calendar_days)rn into l_calendar_daysrn from ( select calendar_daysrn , sum(business_day_ind) rn over ( order by calendar_days ) nrof_bdaysrn from ( select level calendar_daysrn , case to_charrn ( p_start_date+levelrn ,’DY’rn , ‘nls_date_language=english’)rn when ‘SAT’ then 0rn when ‘SUN’ then 0rn else 1rn end as business_day_indrn from dualrn connect by level u0026lt;= p_days * 2rn )rn ) rn where nrof_bdays = p_days;rn return l_calendar_days;rnend count_calendar_days;rnselect idrn, namern, trunc(sysdate) todayrn, daysrn, trunc(sysdate)+count_calendar_days (trunc(sysdate), days) preparedrnfrom productsrnorder by id; rnrnSQLu0026gt; rnSQLu0026gt; select * from vw_products1 order by id;rnrn ID NAME TODAY DAYS PREPARED rnu002du002du002du002d u002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002du002d u002du002du002du002du002du002du002du002du002du002d u002du002du002du002d u002du002du002du002du002du002du002du002du002du002drn 1 Transistor Radio 2017-07-27 1 2017-07-28rn 2 Dial Telephone 2017-07-27 2 2017-07-31rn 3 Popcorn Maker 2017-07-27 5 2017-08-03rn 4 G.I. Joe Figure 2017-07-27 9 2017-08-09rn 5 Beer Cooler 2017-07-27 11 2017-08-11rnu003c/preu003ernThe function in fact iteratively adds calendar days to the starting days, keeps track of the number of actual added u003cemu003ebusinessu003c/emu003e 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 u0026lt;= p_days * 2” equation.rnrnThe 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.rnrnHowever, 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, u003cemu003ein a wayu003c/emu003e, there is:rnu003cpreu003ealter session set nls_territory=’the netherlands’;rnselect refdatern, to_char(refdate,’DAY’, ‘nls_date_language=english’) dayrn, case to_char(refdate,’DY’, ‘nls_date_language=english’)rn when ‘MON’ then 1rn when ‘TUE’ then 2rn when ‘WED’ then 3rn when ‘THU’ then 4rn when ‘FRI’ then 5rn when ‘SAT’ then 6rn when ‘SUN’ then 7rn end as d1rn, to_number(to_char(refdate,’D’)) d2rn, trunc(refdate)-trunc(refdate,’IW’)+1 d3rnfrom ( select sysdate+level-1 as refdate rn from dual rn connect by level u0026lt;= 7rn );rnrnREFDATE DAY D1 D2 D3rnu002du002du002du002du002du002du002du002d u002du002du002du002du002du002du002du002du002du002d u002du002du002du002d u002du002du002du002d u002du002du002du002drn27-07-17 THURSDAY 4 4 4rn28-07-17 FRIDAY 5 5 5rn29-07-17 SATURDAY 6 6 6rn30-07-17 SUNDAY 7 7 7rn31-07-17 MONDAY 1 1 1rn01-08-17 TUESDAY 2 2 2rn02-08-17 WEDNESDAY 3 3 3rnrn7 rows selected. rnu003c/preu003ernThe 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 u003cemu003eiso weeku003c/emu003e format:rnu003cpreu003eselect trunc(sysdate)-trunc(sysdate,’IW’)+1 as drnfrom dual;rnu003c/preu003ernwill 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 u003cemu003eXu003c/emu003e business days to a date starting on a Tuesday, is the same as adding u003cemu003eX+1u003c/emu003e 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 u003cemu003eYu003c/emu003e 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 u003cemu003eYu003c/emu003e, and after that adding the remainder. The first part can be expressed as TRUNC(u003cemu003eYu003c/emu003e/5), the second part as MOD(u003cemu003eYu003c/emu003e,5).rnrnSo 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.rnrnNow 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:rnu003cpreu003ecreate or replace view vw_products asrnselect idrn, namern, todayrn, daysrn, today+offset+7*trunc(days_from_offset/5)+mod(days_from_offset,5) preparedrnfrom ( select prd.*rn , mod(12-d,7)-4 offsetrn , days+greatest(mod(d+4,5),0) days_from_offsetrn from ( select prd.*rn , trunc(sysdate) todayrn , trunc(sysdate)-trunc(sysdate,’IW’)+1 drn from products prdrn ) prdrn ) prd rnorder by id;u003c/preu003e