I am using the following query to find out the number of working days in a month excluding the weekends
select payroll_id as payrollId,
(select count(*)
from ( select rownum rnum
from all_objects
where rownum <= to_date(to_char(last_day(to_date('01-'||'MAY'||'2017','DD-MM-YYYY')),'DD')||'MAY'||'2017','DD-MM-YYYY') - to_date('01-'||'MAY'||'2017','DD-MM-YYYY')+1 )
where to_char( to_date('01-'||'MAY'||'2017','DD-MM-YYYY')+rnum-1, 'DY' )
not in ( 'SAT', 'SUN' )) - (select count(*) from admin_holiday where to_char(holiday_date,'DD-MON-YYYY') like '%-MAY-2017%' and holiday_type_id=1) days
from employee where DEL_FLAG=1 order by payrollId
Here, payrollId is the employee_id in employee table and admin_holiday is a table that contains information about the national holidays in a month
My requirement is that if the month is current month then the working days should be upto today's date. For example the current month is MAY/2017 and till today(i.e 22/05/2017) the working days count excluding weekends is 15 (10/05/2017 is a national holiday according to Hindu calendar).
And if it is not the current month then should calculate based on above sql logic
How do I obtain the required result.
***NOTE*** this action has to be performed in a single select statement and no other pl/sql blocks can be used, etc.