create table calendar_dates (
calendar_day date not null primary key,
is_working_day varchar2(1) not null,
constraint is_midnight check ( calendar_day = trunc ( calendar_day ) )
)Table created.
insert into calendar_dates
with dates as (
select date'2007-12-31'+level dt
from dual
connect by level <= 7300
)
select dt,
case
when to_char(dt, 'fmday') in ('sunday', 'saturday')
then 'N' else 'Y'
end
from dates7300 row(s) inserted.
commitStatement processed.
Find Next Working Day Case Expression
select calendar_day,
to_char(calendar_day, 'fmday') dy,
case
when to_char(calendar_day, 'fmday') = 'friday' then
calendar_day + 3
when to_char(calendar_day, 'fmday') = 'saturday' then
calendar_day + 2
else
calendar_day + 1
end next_day
from calendar_dates
where calendar_day between date'2018-01-01' and date'2018-01-07'| CALENDAR_DAY | DY | NEXT_DAY | 01-JAN-18 | monday | 02-JAN-18 | 02-JAN-18 | tuesday | 03-JAN-18 | 03-JAN-18 | wednesday | 04-JAN-18 | 04-JAN-18 | thursday | 05-JAN-18 | 05-JAN-18 | friday | 08-JAN-18 | 06-JAN-18 | saturday | 08-JAN-18 | 07-JAN-18 | sunday | 08-JAN-18 |
|---|
Add or Subtract N Working Days Loop
create or replace function add_n_working_days (
start_date date, working_days pls_integer
) return date as
start_date_midnight date := trunc ( start_date );
end_date date := start_date_midnight;
counter pls_integer := 0;
begin
if working_days = 0 then
end_date := start_date_midnight;
elsif to_char(start_date_midnight, 'fmdy') in ('sat', 'sun') then
/* Move to next Monday when adding;
previous Friday when subtracting */
if sign(working_days) = 1 then
end_date := next_day(start_date_midnight, 'monday');
else
end_date := next_day(start_date_midnight-7, 'friday');
end if;
end if;
while (counter < abs(working_days)) loop
end_date := end_date + sign(working_days);
if to_char(end_date, 'fmdy') not in ('sat', 'sun') then
counter := counter + 1;
end if;
end loop;
return end_date;
end add_n_working_days; Function created.
Add or Subtract N Working Days Optimized Loop
create or replace function add_n_working_days_optimized (
start_date date, working_days pls_integer
) return date as
end_date date;
start_date_midnight date := trunc ( start_date );
counter pls_integer := 0;
remaining_days pls_integer;
weeks pls_integer;
begin
if working_days = 0 then
end_date := start_date_midnight;
elsif to_char(start_date_midnight, 'fmdy') in ('sat', 'sun') then
if sign(working_days) = 1 then
end_date := next_day(start_date_midnight, 'monday');
else
end_date := next_day(start_date_midnight-7, 'friday');
end if;
else
end_date := start_date_midnight;
end if;
if abs(working_days) <= 5 then
remaining_days := working_days;
else
weeks := floor ( abs(working_days) / 5 ) * sign(working_days);
end_date := end_date + ( weeks * 7 );
remaining_days := mod ( working_days, 5 );
end if;
while (counter < abs(remaining_days)) loop
end_date := end_date + sign(working_days);
if to_char(end_date, 'fmdy') not in ('sat', 'sun') then
counter := counter + 1;
end if;
end loop;
return end_date;
end add_n_working_days_optimized; Function created.
Get Working Days Between Dates Loop
create or replace function get_working_days_between (
start_date date, end_date date
) return pls_integer as
counter pls_integer := 0;
date_range pls_integer;
begin
date_range := end_date - start_date;
for dys in 1 .. abs( date_range ) loop
if to_char (
start_date + ( dys * sign ( date_range ) ), 'fmdy'
) not in ('sat', 'sun') then
counter := counter + 1;
end if;
end loop;
return counter;
end get_working_days_between; Function created.
Add or Subtract N Working Days SQL Loop
create or replace function add_n_working_days_sql_loop (
start_date date, working_days pls_integer
) return date as
end_date date;
start_date_midnight date := trunc ( start_date );
counter pls_integer := 0;
date_cur sys_refcursor;
date_rec calendar_dates%rowtype;
begin
if working_days = 0 then
end_date := start_date_midnight;
elsif working_days < 0 then
open date_cur for
select * from calendar_dates
where calendar_day < start_date_midnight
and is_working_day = 'Y'
order by calendar_day desc;
else
open date_cur for
select * from calendar_dates
where calendar_day > start_date_midnight
and is_working_day = 'Y'
order by calendar_day;
end if;
loop
fetch date_cur into date_rec;
end_date := date_rec.calendar_day;
counter := counter + 1;
exit when counter > abs ( working_days );
end loop;
close date_cur;
return end_date;
end add_n_working_days_sql_loop; Function created.
Add or Subtract N Working Days SQL Loop Bulk Collection
create or replace function add_n_working_days_sql_bulk (
start_date date, working_days pls_integer
) return date as
end_date date;
start_date_midnight date := trunc ( start_date );
counter pls_integer := 0;
date_cur sys_refcursor;
fetch_limit pls_integer := 100;
type date_arr is table of
calendar_dates%rowtype index by pls_integer;
date_rec date_arr;
beyond_date_limit exception;
begin
if working_days = 0 then
end_date := start_date_midnight;
elsif working_days < 0 then
open date_cur for
select * from calendar_dates
where calendar_day <= start_date_midnight
and is_working_day = 'Y'
order by calendar_day desc;
else
open date_cur for
select * from calendar_dates
where calendar_day >= start_date_midnight
and is_working_day = 'Y'
order by calendar_day;
end if;
if date_cur%isopen then
<<row_loop>> loop
fetch date_cur bulk collect into date_rec limit fetch_limit;
if date_rec.count = 0 then
raise beyond_date_limit;
end if;
counter := counter + 1;
if abs (working_days) < ( counter * fetch_limit ) then
end_date := date_rec( mod ( abs (working_days), fetch_limit ) + 1 ).calendar_day;
exit row_loop ;
else
end_date := date_rec( date_rec.last ).calendar_day;
end if;
end loop;
close date_cur;
end if;
return end_date;
exception
when beyond_date_limit then
raise_application_error(-20001, 'Date: ' || start_date || ' add ' || working_days || ' out of range');
end add_n_working_days_sql_bulk; Function created.
Add or Subtract N Working Days SQL
create or replace function add_n_working_days_sql (
start_date date, working_days pls_integer
) return date as
end_date date;
start_date_midnight date := trunc ( start_date );
begin
if working_days = 0 then
end_date := start_date_midnight;
elsif working_days > 0 then
with dates as (
select * from calendar_dates
where calendar_day >= start_date_midnight
and is_working_day = 'Y'
), plus_n_days as (
select lead(calendar_day, working_days)
over (order by calendar_day) dt
from dates
)
select min(dt) into end_date from plus_n_days;
else
with dates as (
select * from calendar_dates
where calendar_day <= start_date_midnight
and is_working_day = 'Y'
), minus_n_days as (
select lag(calendar_day, abs(working_days))
over (order by calendar_day) dt
from dates
)
select max(dt) into end_date from minus_n_days;
end if;
return end_date;
end add_n_working_days_sql; Function created.
Add or Subtract N Working Days SQL Optimized
create or replace function add_n_working_days_sql_optimized (
start_date date, working_days pls_integer
) return date as
end_date date;
start_date_midnight date := trunc ( start_date );
safety_margin pls_integer := 10;
begin
if working_days = 0 then
end_date := start_date_midnight;
elsif working_days > 0 then
with dates as (
select * from calendar_dates
where calendar_day between start_date_midnight
and start_date_midnight + ( working_days / 5 * 7 ) + safety_margin
and is_working_day = 'Y'
), plus_n_days as (
select lead(calendar_day, working_days)
over (order by calendar_day) dt
from dates
)
select min(dt) into end_date from plus_n_days;
else
with dates as (
select * from calendar_dates
where calendar_day between start_date_midnight + ( working_days / 5 * 7 ) - safety_margin
and start_date_midnight
and is_working_day = 'Y'
), minus_n_days as (
select lag(calendar_day, abs(working_days))
over (order by calendar_day) dt
from dates
)
select max(dt) into end_date from minus_n_days;
end if;
return end_date;
end add_n_working_days_sql_optimized; Function created.
Get Working Days Between Dates SQL
create or replace function get_working_days_between_sql (
start_date date, end_date date
) return pls_integer as
num_days pls_integer := 0;
begin
select count(*)
into num_days
from calendar_dates
where is_working_day = 'Y'
and calendar_day > trunc ( start_date )
and calendar_day <= trunc ( end_date );
return num_days;
end get_working_days_between_sql; Function created.
with rws as (
select rownum-15 x from dual
connect by level <= 31
), dates as (
select date'2018-03-01' + (level/3) dt from dual
connect by level <= 45
), add_n as (
select x, dt,
add_n_working_days(dt, x) lp,
add_n_working_days_optimized(dt, x) opt,
add_n_working_days_sql(dt, x) sq,
add_n_working_days_sql_bulk(dt, x) bk,
add_n_working_days_sql_optimized(dt, x) sopt
from rws
cross join dates
)
select * from add_n
where lp <> opt
or opt <> sq
or bk <> sq
or sopt <> bk
or sopt is nullno data found
create or replace procedure time_add_n (
fn_name varchar2, days int
) as
res date;
start_time pls_integer;
run_time pls_integer;
iterations pls_integer := 100000;
assert_fn_name varchar2(ora_max_name_len);
begin
/* SQL injection check */
assert_fn_name := dbms_assert.sql_object_name (fn_name);
start_time := dbms_utility.get_time();
for i in 1 .. iterations loop
execute immediate 'begin
:res := ' || assert_fn_name || '(date''2018-01-01'', :days) ;
end;' using out res, days;
end loop;
run_time := dbms_utility.get_time() - start_time;
/* Output the time / execution in hsecs */
dbms_output.put_line('N = ' || days || ' time ' ||
run_time || ' /exec ' || ( run_time / iterations ) || ' hsecs'
);
end time_add_n; Procedure created.
Performance Test Script
begin
dbms_output.put_line ( ' **** add_n_working_days **** ' );
time_add_n('add_n_working_days', 1);
time_add_n('add_n_working_days', 5);
/* time_add_n('add_n_working_days', 10);
time_add_n('add_n_working_days', 20);
time_add_n('add_n_working_days', 50);
time_add_n('add_n_working_days', 75);
time_add_n('add_n_working_days', 100);
dbms_output.put_line ( ' **** add_n_working_days_optimized **** ' );
time_add_n('add_n_working_days_optimized', 1);
time_add_n('add_n_working_days_optimized', 5);
time_add_n('add_n_working_days_optimized', 10);
time_add_n('add_n_working_days_optimized', 50);
time_add_n('add_n_working_days_optimized', 100);
dbms_output.put_line ( ' **** add_n_working_days_sql **** ' );
time_add_n('add_n_working_days_sql', 1);
time_add_n('add_n_working_days_sql', 5);
time_add_n('add_n_working_days_sql', 10);
time_add_n('add_n_working_days_sql', 50);
time_add_n('add_n_working_days_sql', 100);
dbms_output.put_line ( ' **** add_n_working_days_sql_loop **** ' );
time_add_n('add_n_working_days_sql_loop', 1);
time_add_n('add_n_working_days_sql_loop', 5);
time_add_n('add_n_working_days_sql_loop', 10);
time_add_n('add_n_working_days_sql_loop', 50);
time_add_n('add_n_working_days_sql_loop', 100);
dbms_output.put_line ( ' **** add_n_working_days_sql_bulk **** ' );
time_add_n('add_n_working_days_sql_bulk', 1);
time_add_n('add_n_working_days_sql_bulk', 5);
time_add_n('add_n_working_days_sql_bulk', 10);
time_add_n('add_n_working_days_sql_bulk', 50);
time_add_n('add_n_working_days_sql_bulk', 100);
dbms_output.put_line ( ' **** add_n_working_days_sql_optimized **** ' );
time_add_n('add_n_working_days_sql_optimized', 1);
time_add_n('add_n_working_days_sql_optimized', 5);
time_add_n('add_n_working_days_sql_optimized', 10);
time_add_n('add_n_working_days_sql_optimized', 50);
time_add_n('add_n_working_days_sql_optimized', 100);
*/
end; **** add_n_working_days ****
N = 1 time 645 /exec .00645 hsecs
N = 5 time 694 /exec .00694 hsecs