Add business days
It starts out as a fairly simple, innocent business requirement. Create a report to list records meeting some criteria, one of which is:
“List only records where today’s date is more than 35 business days after the due date from the record.”
When you delve deeper you find that querying the table with “DUE_DATE + 35 < SYSDATE
” is not going to cut it – “business days” do not include weekends. You might start with something similar to this. But even that’s not good enough, because business days should not include public holidays. How do you code that?
So, here’s my solution.
1. We need to know what days are public holidays for the region. In our case this application is only applicable for a single region, so we use a simple table:
CREATE TABLE holidays (holiday_date DATE PRIMARY KEY);
We create a simple form for users to enter new holidays every year, and give someone the job of making sure it’s up-to-date every year when the public holidays are announced.
2. Create a view that lists all non-business days – i.e. list all weekends and public holidays. To retain reasonable performance, we limit our solution to dates in the years 2000 to 2050.
CREATE VIEW non_business_days AS SELECT TO_DATE('01012000','DDMMYYYY') + ROWNUM * 7 AS day -- Saturdays 2000 to 2050 FROM DUAL CONNECT BY LEVEL <= 2661 UNION ALL SELECT to_date('02012000','DDMMYYYY') + ROWNUM * 7 AS day -- Sundays 2000 to 2050 FROM DUAL CONNECT BY LEVEL <= 2661 UNION ALL SELECT holiday_date FROM holidays;
3. Now, when we need to take a date and add x business days to it, we query this table to find all the non-business-days that are applicable, e.g.:
SELECT day ,COUNT(*) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS count_so_far ,(day - p_date) AS base_days FROM NON_BUSINESS_DAYS WHERE day > p_date;
If you run this query and examine each row in order of day, if you take base_days and subtract count_so_far, when the result is less than x, then base_days – count_so_far is the number of extra days we need to add to the holiday’s date to give us the answer. You’ll find this logic in the function below.
In our final solution, we’ll also need to UNION in the date parameter as well, for the case where there are no holidays between the starting date and the number of business days requested.
Here’s our function to take any date (at least, any date between 2000 and 2050) and add x business days (positive or negative):
FUNCTION add_working_days (p_date IN DATE, p_working_days IN NUMBER) RETURN DATE IS l_date DATE; BEGIN IF p_date IS NULL OR p_working_days IS NULL THEN RETURN NULL; END IF; IF p_working_days != TRUNC(p_working_days) THEN RAISE_APPLICATION_ERROR(-20000, 'add_working_days: cannot handle fractional p_working_days (' || p_working_days || ')'); END IF; IF p_working_days > 0 THEN SELECT MAX(day + p_working_days - (base_days - count_so_far)) INTO l_date FROM (SELECT day ,COUNT(*) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS count_so_far ,(day - p_date) AS base_days FROM NON_BUSINESS_DAYS WHERE day > p_date UNION SELECT p_date, 0, 0 FROM DUAL ) WHERE base_days - count_so_far < p_working_days; ELSIF p_working_days < 0 THEN SELECT MIN(day - (ABS(p_working_days) - (base_days - count_so_far))) INTO l_date FROM (SELECT day ,COUNT(*) OVER (ORDER BY day DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS count_so_far ,(p_date - day) AS base_days FROM NON_BUSINESS_DAYS WHERE day < p_date UNION SELECT p_date, 0, 0 FROM DUAL ) WHERE base_days - count_so_far < ABS(p_working_days); ELSE l_date := p_date; END IF; RETURN l_date; END add_working_days;
Test cases (these are some public holidays in Western Australia):
insert into holidays values (to_date('27/12/2010','DD/MM/YYYY'); insert into holidays values (to_date('28/12/2010','DD/MM/YYYY'); insert into holidays values (to_date('03/01/2011','DD/MM/YYYY'); insert into holidays values (to_date('26/01/2011','DD/MM/YYYY');
— Expected: 06/01/2011
select cls_util.add_working_days(to_date('13/12/2010','DD/MM/YYYY') ,15) from dual;
— Expected: 31/01/2011
select cls_util.add_working_days(to_date('25/01/2011','DD/MM/YYYY') ,3) from dual;
— Expected: 13/12/2010
select cls_util.add_working_days(to_date('06/01/2011','DD/MM/YYYY') ,-15) from dual;
— Expected: 25/01/2011
select cls_util.add_working_days(to_date('31/01/2011','DD/MM/YYYY') ,-3) from dual;
Islam Abbas
9 September 2011 - 5:07 am
Interesting and very useful, thank you so much
KA J
27 December 2017 - 11:26 am
Is the date needs to be the Primary Key for Holidays table for this to work? Thanks
Jeffrey Kemp
27 December 2017 - 3:10 pm
It requires a unique constraint at least.