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

Kalender deel 1: Erkende feestdagen

Om van een bepaald jaar de erkende feestdagen van Nederland te bepalen gebruik ik al jaren hetzelfde stukje PL/SQL-code. De aankomende troonswisseling, en daarmee het wijzigen van de nationale feestdag Koninginnedag naar Koningsdag, was voor mij de aanleiding om de bewuste code eens te reviseren.

Nederland kent de volgende elf erkende feestdagen:

De ‘blauwe’ feestdagen vallen ieder jaar op dezelfde datum, de ‘groene’ feestdagen zijn alle af te leiden uit de 1e Paasdag en de dag waarop Nederland Koning(s)(inne)dag viert, wordt bepaald door de verjaardag van de regerende vorst.

1e Paasdag oftewel paaszondag is met behulp van het algoritme van J.M. Oudin (1940) te berekenen. Geldig voor de Gregoriaanse kalender, dus vanaf 1583. In de PL/SQL-code hieronder heb ik het algoritme uitgewerkt.

create or replace function easter_sunday
(p_year number := to_char(sysdate,'yyyy'))
return date
/**********************
* 2013 www.orcado.nl *
**********************/
is
l1 pls_integer;
l2 pls_integer;
l3 pls_integer;
l4 pls_integer;
l5 pls_integer;
l6 pls_integer;
l7 pls_integer;
begin
if p_year < 1583 then
return null;
end if;
l1 := trunc(p_year / 100);
l2 := mod(p_year,19);
l3 := l1 - trunc(l1 / 4);
l4 := mod(l3 - trunc((l1 - trunc((l1 - 17) / 25)) / 3) + 19 * l2 + 15,30);
l5 := trunc(l4 / 28);
l6 := l4 - l5 * (1 - l5 * trunc(29 / (l4 + 1)) * trunc((21 - l2) / 11));
l7 := mod(p_year + trunc(p_year/4) + l6 + 2 - l3,7);
return to_date('0103' || p_year,'ddmmyyyy') + l6 - l7 + 27;
end easter_sunday;
/

SQL> select easter_sunday from dual;

EASTER_SUNDAY
-------------
31-03-2013

SQL> select easter_sunday(2025) from dual;

EASTER_SUNDAY(2025)
-------------------
20-04-2025

Koninginnedag doet zijn intrede op 31 augustus 1898 toen Wilhelmina koningin van Nederland werd. Sinds 1949 is Koninginnedag op de verjaardag van koningin Juliana, 30 april. Vanaf 2014 zal Koningsdag gevierd gaan worden op 27 april, de verjaardag van dan koning Willem-Alexander. Wanneer vanaf 1949 Koning(s)(inne)dag op een zondag valt, wordt het één dag verplaatst. Tot 1980 werd Koninginnedag verplaatst naar maandag 1 mei. Sinds 1980 wordt het de dag ervoor op zaterdag gevierd. Het bepalen van Koning(s)(inne)dag heb ik als volgt uitgewerkt.

create or replace function kingsday
(p_year number := to_char(sysdate,'yyyy'))
return date
/**********************
* 2013 www.orcado.nl *
**********************/
is
l_day  varchar2(4);
l_date date;
begin
if p_year < 1898 then
return null;
elsif p_year < 1949 then
l_day := '3108';
elsif p_year < 2014 then
l_day := '3004';
else
l_day := '2704';
end if;
l_date := to_date(l_day || p_year,'ddmmyyyy');
if p_year > 1948
and to_char(l_date,'dy','nls_date_language=dutch') = 'zo' then
l_date := l_date + sign(19795 / 10 - p_year);
end if;
return l_date;
end kingsday;
/

SQL> select kingsday from dual;

KINGSDAY
----------
30-04-2013

SQL> select kingsday(2025) from dual;

KINGSDAY(2025)
--------------
26-04-2025

Bovenstaande stukken code heb ik geïntegreerd in het onderstaande voorbeeld, waarin bij een opgegeven jaar de erkende feestdagen worden getoond. Condities die hierin zijn opgenomen, zijn:

Zo, in ieder geval tot de volgende troonsopvolging van prinses Amalia is de code weer up-to-date.

Lees verder in deel 2.

Hier komt de sidebar

Volg ons op

© Orcado B.V. | 1999 - 2013