Most of us would have come across this task. ie., to find the number of working days between two given dates.
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.
When I Google it, got this
I like the logic and the performance is improved but not up-to expectation. In my case
returns a huge list. Also there is a restriction to the maximum output. ie., the all_objects count itself.
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)
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.
Method 3 and Method 4 can be used depends on the situation.