The Stupid Box

Most of us would have come across this task. ie., to find the number of working days between two given dates.

Method 1
I got a function to calculate the same from my colleague.

function getDays(d1 IN DATE, d2 IN DATE) RETURN INTEGER
IS
   l_result INTEGER := 0;
BEGIN
   FOR r IN (1 .. trunc(d2)-trunc(d1)) LOOP
       IF (to_char(trunc(d1)+r),'DY') NOT IN ('SAT','SUN')) THEN
              l_result := l_result + 1;
       END IF
   END LOOP;
   RETURN l_result;
END;

The algorithm involved is to iterate through the number of days between the two dates and check each day if it is a working day or not. It works neatly but performance is very poor.

Method 2
When I Google it, got this

I like the logic and the performance is improved but not up-to expectation. In my case

all_objects

returns a huge list. Also there is a restriction to the maximum output. ie., the all_objects count itself.

Method 3
modified the same query little bit like this will not have any restriction also the performance will be better than previous.

select count(*) from
                    (select level rnum from dual connect by level <= to_date('&1') - to_date('&2')+1 ) 
        where to_char( to_date('&2')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' )

Another advantage in using this query technique (over iteration/loop) is, we can also find the number of working days excluding the public holidays/leaves (provided in a table from the system)

Method 4
There is a catch in the above technique, even though it is faster comparatively, not highly recommended to use inside another select query. This one is not a function, instead its a formula 😛 as mentioned in this link. Therefore can be easily used in the any select query.

select 
(trunc(end_dt - start_dt) -
 (
  (case
   WHEN (8-to_number(to_char(start_dt,'D') )) > trunc(end_dt - start_dt)+1
   THEN 0
   ELSE
    trunc( (trunc(end_dt - start_dt) - (8-to_number(to_char(start_dt,'D') ))) / 7 ) + 1
   END) +
  (case
   WHEN mod(8-to_char(start_dt,'D'),7) > trunc(end_dt - start_dt)-1
   THEN 0
   ELSE
    trunc( (trunc(end_dt-start_dt) - (mod(8-to_char(start_dt,'D'),7)+1)) / 7 ) + 1
   END)
 )
) workingdays
from table

This method gives good performance and the only limitation is excluding the working holidays.

Conclusion:
Method 3 and Method 4 can be used depends on the situation.

Happy Quering!!