11-07-2013 door: Michael Bruijn
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:
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
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
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 -
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
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