0

I have two dates and I want to exclude weekends & public holidays. I managed to exclude weekends but can't figure out how to change my function to remove the public holidays

Got a table of Public Holidays 

Table : holidays that looks like this   
     DT_DAY
 - 23/11/2017
 - 02/12/2017

My function that excludes Weekends looks like this : 

create or replace function BusinessDays(start_date in date, end_date in date)
return number is countBusiness number := 0; /* counter for business days */

begin

countBusiness:= (to_date(end_date,'dd-mm-yy')- to_date(start_date, 'dd-mm-yy')) +1
 - (Next_Day(to_date(end_date,'dd-mm-yy'), 'Sunday')
 - Next_Day(to_date(start_date,'dd-mm-yy'), 'Sunday') )/7
 - (Next_Day(to_date(end_date,'dd-mm-yy'), 'Saturday')
 - Next_Day(to_date(start_date,'dd-mm-yy'), 'Saturday') )/7;

 return (countBusiness);
 end;

So if I use BusinessDays(23/11/2017, 27/11/2017) the result is 3 (cause counts today 23/11, tomorrow 24/11 and monday 27/11). And I'd like to exclude days that are on my table holidays so it doesn't count them.... Taking the example I just gave.

BusinessDays(23/11/2017, 27/11/2017) the result I'm looking for is 2 cause 23/11/2017 is on my holidays table...

Praem
  • 107
  • 9
  • Possible duplicate of [Count work days between two dates](https://stackoverflow.com/questions/252519/count-work-days-between-two-dates) – Stavr00 Nov 23 '17 at 16:34

1 Answers1

1

Here is a solution adapted from this:

countBusiness := 
 (to_date(end_date,'dd-mm-yy') - to_date(start_date, 'dd-mm-yy'))+1
 -
 (to_number(to_char(to_date(end_date,'dd-mm-yy'),'IW'))
  -
  to_number(to_char(to_date(start_date,'dd-mm-yy'),'IW'))
 )*2
 -
 (CASE WHEN to_char(to_date(start_date,'dd-mm-yy'),'D')='7' THEN 1 ELSE 0)
 - 
 (CASE WHEN to_char(to_date(end_date,'dd-mm-yy'),'D')='6' THEN 1 ELSE 0)
 -
 (SELECT COUNT(1) FROM holidays 
   WHERE dt_day BETWEEN to_date(start_date,'dd-mm-yy') AND to_date(end_date,'dd-mm-yy'))
;
Stavr00
  • 3,219
  • 1
  • 16
  • 28