Kalender deel 2: Werkdagen

Oracle SQL biedt standaard functionaliteit voor het berekenen van het aantal dagen of het aantal maanden tussen twee datums. Bijvoorbeeld:

SQL> select to_date('16112013','ddmmyyyy') - to_date('06052013','ddmmyyyy') "days" from dual;

days
----------
194

SQL> select months_between(to_date('16112013','ddmmyyyy'), to_date('16052013','ddmmyyyy')) "months" from dual;

months
----------
6

Ook het optellen van dagen of maanden bij een datum is eenvoudig met SQL te doen.

SQL> select to_date('06052013','ddmmyyyy') + 23 "date" from dual;

date
----------
29-05-2013

SQL> select add_months(to_date('06052013','ddmmyyyy'),3) "date" from dual;

date
----------
06-08-2013

Daarentegen is voor het bepalen van het aantal werkdagen tussen twee datums of het optellen van werkdagen bij een datum niet standaard in SQL voorzien. Dus zelf maar iets fabriceren. Het doel is de volgende twee functies:

 

Dagnummers

Voor de flexibiliteit heb ik aan de functies de parameter p_no_business toegevoegd. Hiermee wordt aangegeven welke dagen GEEN werkdagen zijn. De parameter kan combinaties van de volgende dagnummers bevatten:

1 = maandag
2 = dinsdag
3 = woensdag
4 = donderdag
5 = vrijdag
6 = zaterdag
7 = zondag
0 = feestdag

De default waarde van p_no_business is “067”. In dit geval worden de zater-, zon- en feestdagen niet gekenmerkt als een werkdag.

De voor de hand liggende manier om de dagnummer van een datum te achterhalen is met: to_char(p_date,'d'). Deze wijze is echter afhankelijk van de Oracle parameter NLS_TERRITORY. Ik gebruik daarom liever de formule: trunc(sysdate) - trunc(sysdate,'IW'). Onderstaande query maakt dit duidelijk.

SQL> alter session set nls_territory = 'AMERICA';

SQL> select value nls_territory
, to_char(sysdate,'day') day
, to_char(sysdate,'d') no_1
, trunc(sysdate) - trunc(sysdate,'IW') + 1 no_2
from   v$nls_parameters
where  parameter = 'NLS_TERRITORY';

SQL> alter session set nls_territory = 'THE NETHERLANDS';

SQL> select value nls_territory
, to_char(sysdate,'day') day
, to_char(sysdate,'d') no_1
, trunc(sysdate) - trunc(sysdate,'IW') + 1 no_2
from   v$nls_parameters
where  parameter = 'NLS_TERRITORY';

NLS_TERRITORY   DAY     NO_1 NO_2
--------------- ------- ---- ----
AMERICA         maandag    2    1

NLS_TERRITORY   DAY     NO_1 NO_2
--------------- ------- ---- ----
THE NETHERLANDS maandag    1    1

 

Feestdagen

Indien parameter p_no_business “0” bevat, is het van belang de feestdagen te kunnen bepalen. Dit staat beschreven in mijn vorige blog; wat resulteert in de volgende code.

create or replace package calendar
/**********************
* 2013 www.orcado.nl *
**********************/
is
type holiday_rec_typ is record(holiday_name varchar2(25)
,holiday_date date);
type holiday_tab_typ is table of holiday_rec_typ;
end calendar;
/

create or replace function holidays
(p_year in pls_integer)
return calendar.holiday_tab_typ pipelined
/**********************
* 2013 www.orcado.nl *
**********************/
is
l_easter_sunday date := easter_sunday(p_year);
l_kingsday date := kingsday(p_year);
function set
(p_name in varchar2
,p_date in date)
return calendar.holiday_rec_typ
is
l_rec calendar.holiday_rec_typ;
begin
l_rec.holiday_name := p_name;
l_rec.holiday_date := p_date;
return l_rec;
end set;
begin
if p_year > 1952 then
pipe row(set('Nieuwjaarsdag',to_date('0101'||p_year,'ddmmyyyy')));
pipe row(set('1e Kerstdag',to_date('2512'||p_year,'ddmmyyyy')));
pipe row(set('2e Kerstdag',to_date('2612'||p_year,'ddmmyyyy')));
end if;
if p_year > 1989 then
pipe row(set('Bevrijdingsdag',to_date('0505'||p_year,'ddmmyyyy')));
end if;
if l_kingsday is not null then
pipe row(set('Koning' || (case when p_year < 2014 then 'inne' else 's' end) || 'dag',l_kingsday));
end if;
if l_easter_sunday is not null then
pipe row(set('Goede vrijdag',l_easter_sunday - 2));
pipe row(set('1e Paasdag',l_easter_sunday));
pipe row(set('2e Paasdag',l_easter_sunday + 1));
pipe row(set('Hemelvaartsdag',l_easter_sunday + 39));
pipe row(set('1e Pinksterdag',l_easter_sunday + 49));
pipe row(set('2e Pinksterdag',l_easter_sunday + 50));
end if;
return;
end holidays;
/

SQL> select holiday_name "Name"
, to_char(holiday_date,'dy dd-mm-yyyy') "Date"
from table(holidays(2013))
order by holiday_date;

Name            Date
--------------- -------------
Nieuwjaarsdag   di 01-01-2013
Goede vrijdag   vr 29-03-2013
1e Paasdag      zo 31-03-2013
2e Paasdag      ma 01-04-2013
Koninginnedag   di 30-04-2013
Bevrijdingsdag  zo 05-05-2013
Hemelvaartsdag  do 09-05-2013
1e Pinksterdag  zo 19-05-2013
2e Pinksterdag  ma 20-05-2013
1e Kerstdag     wo 25-12-2013
2e Kerstdag     do 26-12-2013

 

check_business_day

Het voorgaande over dagnummers en feestdagen verwerk ik in een functie, check_business_day, die kan achterhalen of een gegeven dag een werkdag is.

create or replace function check_business_day
(p_date        in date
,p_no_business in varchar2 := '067')
return boolean
/**********************
* 2013 www.orcado.nl *
**********************/
is
l_day_no pls_integer := trunc(p_date) - trunc(p_date,'IW') + 1;
l_dummy  pls_integer;
begin
if instr(p_no_business,l_day_no) > 0 then
return false;
end if;
if instr(p_no_business,'0') > 0 then
begin
select 1
into   l_dummy
from   table(holidays(to_char(p_date,'yyyy')))
where  trunc(holiday_date) = trunc(p_date);
return false;
exception
when others then
null;
end;
end if;
return true;
end check_business_day;
/

SQL> set serveroutput on

SQL> declare
procedure p
(p_date in varchar2
,p_no_business in varchar2)
is
l_date date := to_date(p_date,'ddmmyyyy');
begin
dbms_output.put_line(to_char(l_date,'dy dd-mm-yyyy')
|| ' '
|| rpad(p_no_business,13)
|| (case when check_business_day(l_date,p_no_business)
then '+'
else '-'
end));
end p;
begin
dbms_output.put_line('Date No business Business day');
dbms_output.put_line('-------------- ------------ ------------');
p('09052013','067');
p('10052013','067');
p('11052013','067');
p('12052013','067');
p('09052013','07');
p('10052013','07');
p('11052013','07');
p('12052013','07');
p('09052013','7');
p('10052013','7');
p('11052013','7');
p('12052013','7');
end;
/

Date           No business  Business day
-------------- ------------ ------------
do 09-05-2013  067          -
vr 10-05-2013  067          +
za 11-05-2013  067          -
zo 12-05-2013  067          -
do 09-05-2013  07           -
vr 10-05-2013  07           +
za 11-05-2013  07           +
zo 12-05-2013  07           -
do 09-05-2013  7            +
vr 10-05-2013  7            +
za 11-05-2013  7            +
zo 12-05-2013  7            -

 

business_days_between

Een eenvoudige manier om nu het aantal werkdagen tussen twee datums te berekenen is gebruik te maken van een PL/SQL-loop en de functie check_business_day.

create or replace function business_days_between
(p_start_date  in date
,p_end_date    in date
,p_no_business in varchar2 := '067')
return pls_integer
/**********************
* 2013 www.orcado.nl *
**********************/
is
j pls_integer := 0;
begin
for i in 0..(trunc(p_end_date) - trunc(p_start_date)) loop
if check_business_day(p_start_date + i,p_no_business) then
j := j + 1;
else
if i = 0 then
j := 1;
end if;
end if;
end loop;
return j - 1;
end business_days_between;
/

SQL> def p_date = "13052013"

SQL> select to_char(to_date('&p_date','ddmmyyyy'),'dy dd-mm-yyyy') "start date"
, to_char(to_date('&p_date','ddmmyyyy') + level - 1,'dy dd-mm-yyyy') "end date"
, business_days_between(to_date('&p_date','ddmmyyyy')
,to_date('&p_date','ddmmyyyy') + level - 1
,'67') "business days"
from dual
connect by level <= 20;

start date    end date      business days
------------- ------------- -------------
ma 13-05-2013 ma 13-05-2013             0
ma 13-05-2013 di 14-05-2013             1
ma 13-05-2013 wo 15-05-2013             2
ma 13-05-2013 do 16-05-2013             3
ma 13-05-2013 vr 17-05-2013             4
ma 13-05-2013 za 18-05-2013             4
ma 13-05-2013 zo 19-05-2013             4
ma 13-05-2013 ma 20-05-2013             5
ma 13-05-2013 di 21-05-2013             6
ma 13-05-2013 wo 22-05-2013             7
ma 13-05-2013 do 23-05-2013             8
ma 13-05-2013 vr 24-05-2013             9
ma 13-05-2013 za 25-05-2013             9
ma 13-05-2013 zo 26-05-2013             9
ma 13-05-2013 ma 27-05-2013            10
ma 13-05-2013 di 28-05-2013            11
ma 13-05-2013 wo 29-05-2013            12
ma 13-05-2013 do 30-05-2013            13
ma 13-05-2013 vr 31-05-2013            14
ma 13-05-2013 za 01-06-2013            14

 

add_business_days

Op een vergelijkbare wijze is het ook eenvoudig een aantal werkdagen bij een datum op te tellen. Indien het aantal werkdagen is 0, dan wordt de eerst volgende werkdag geretourneerd.

create or replace function add_business_days
(p_date        in date
,p_number      in pls_integer
,p_no_business in varchar2 := '067')
return date
/**********************
* 2013 www.orcado.nl *
**********************/
is
i pls_integer := 0;
j pls_integer := 0;
begin
if p_number = 0 then
while not check_business_day(p_date + i,p_no_business) loop
i := i + 1;
end loop;
else
while j < abs(p_number) loop
i := i + sign(p_number);
if check_business_day(p_date + i,p_no_business) then
j := j + 1;
end if;
end loop;
end if;
return p_date + i;
end add_business_days;
/

SQL> def p_date = "13052013"

SQL> select to_char(to_date('&p_date','ddmmyyyy'),'dy dd-mm-yyyy') "start date"
, level - 1 "days"
, to_char(add_business_days(to_date('&p_date','ddmmyyyy')
,level - 1
,'67'),'dy dd-mm-yyyy') "end date"
from dual
connect by level <= 20;

start date          days end date
------------- ---------- -------------
ma 13-05-2013          0 ma 13-05-2013
ma 13-05-2013          1 di 14-05-2013
ma 13-05-2013          2 wo 15-05-2013
ma 13-05-2013          3 do 16-05-2013
ma 13-05-2013          4 vr 17-05-2013
ma 13-05-2013          5 ma 20-05-2013
ma 13-05-2013          6 di 21-05-2013
ma 13-05-2013          7 wo 22-05-2013
ma 13-05-2013          8 do 23-05-2013
ma 13-05-2013          9 vr 24-05-2013
ma 13-05-2013         10 ma 27-05-2013
ma 13-05-2013         11 di 28-05-2013
ma 13-05-2013         12 wo 29-05-2013
ma 13-05-2013         13 do 30-05-2013
ma 13-05-2013         14 vr 31-05-2013
ma 13-05-2013         15 ma 03-06-2013
ma 13-05-2013         16 di 04-06-2013
ma 13-05-2013         17 wo 05-06-2013
ma 13-05-2013         18 do 06-06-2013
ma 13-05-2013         19 vr 07-06-2013