Hello,
I have the following pl/sql function that generates a count of working days between two dates selected (i.e. to exclude weekends). However, I also need it to exclude specific holidays - Christmas day etc. Dates of these holidays are stored in a table in our database (11GR2) called 'HOLIDAYS'.
How can I integrate holiday dates held in the HOLIDAYS table into the following query to exclude these dates as well? I know how to write a separate pl/sql funtion to get a count of holidays between two dates using SELECT...INTO but I can't work out how to bring them together into one query.
Could anyone show me how with the pl/sql / dates below please?
Sample HOLIDAYS table below.
Thanks!
TP
create or replace
function WORKING_DAYS (pi_start_date in date, pi_end_date in date) return integer
is
v_start_date date :=pi_start_date;
v_end_date date:=pi_end_date;
v_count integer:=0;
begin
while v_start_date <= v_end_date
loop
if to_char(v_start_date,'D') not in ('6','7')
then
v_count := v_count+1;
end if;
v_start_date:=v_start_date+1;
end loop;
return v_count;
end;
(select '10-Apr-2013' as NWD from dual union all
select '06-May-2013' from dual union all
select '27-May-2013' from dual union all
select '26-Aug-2013' from dual union all
select '26-Dec-2013' from dual union all
select '25-Dec-2013' from dual union all
select '01-Jan-2014' from dual union all
select '18-Apr-2014' from dual union all
select '21-Apr-2014' from dual union all
select '05-May-2014' from dual union all
select '26-May-2014' from dual union all
select '25-Aug-2014' from dual union all
select '25-Dec-2014' from dual union all
select '26-Dec-2014' from dual) HOLIDAYS