How to calculate working days for an organization , if the organization works for only a particular number of week in a month:-
We will create a function which will take current date of calendar as input and check if this input date's week is same as the one which is required.If yes then we will return '1' else
we will return '0'.
we are removing saturday and sunday as weekends and also we are removing the week with minimum number of working days and then again re-numbering the weeks as per number of days .
IS_DEPARTMENT_WORKING(V_DATE NUMBER,wnum number) RETURN NUMBER IS
cursor c_rec is Select CNT,week_of_month,row_number() over (order by count(*) desc,week_of_month) from
(select cnt,week_of_month,
MIN(week_of_month) KEEP (DENSE_RANK FIRST ORDER BY cnt) OVER () Lowest
from(select count(*) cnt ,week_of_month ,row_number() over (order by count(*) desc,week_of_month)
from (
select x dt,
(to_char( x+1, 'iw') - to_char(to_date('01/'||to_char(x,'mon/')||to_char(x,'yyyy'),'dd/mon/yyyy') +
1,'iw') + 1)
Week_of_month
from
(select TRUNC(TO_DATE((V_DATE ,'YYYYMMDD'), 'MONTH')+rownum-1 x
from all_objects
where rownum <= 50
)
where to_char(x,'dy','nls_date_language=english') not in ('sat','sun')
and to_char(x,'mon')=to_char(TO_DATE((V_DATE ,'YYYYMMDD'),'mon'))
group by week_of_month) )
where ((week_of_month<>nvl(lowest,0) AND CNT <5) OR (CNT=5))
GROUP BY CNT,WEEK_OF_MONTH;
cursor c_rec2 is select x dt,
(to_char( x+1, 'iw') - to_char(to_date('01/'||to_char(x,'mon/')||to_char(x,'yyyy'),'dd/mon/yyyy') +
1,'iw') + 1)
Week_of_month
from
(select TRUNC(TO_DATE((V_DATE ,'YYYYMMDD'), 'MONTH')+rownum-1 x
from all_objects
where rownum <= 35
)
where to_char(x,'dy','nls_date_language=english') not in ('sat','sun')
and to_char(x,'mon')=to_char(TO_DATE((V_DATE ,'YYYYMMDD'),'mon');
cnt_wdays number(5);
wnumber number(2);
worgnumber number(2);
date1 DATE;
wnumber1 number(2);
flg number(1);
BEGIN
open c_rec ;
open c_rec2;
loop
fetch c_rec into cnt_wdays,worgnumber,wnumber;
if wnum=wnumber then
loop
fetch c_rec2 into date1,wnumber1;
if TO_NUMBER(TO_CHAR(date1,'YYYYMMDD'))=trunc((V_DATE ) and wnumber1=worgnumber
then
flg:=1;
exit;
end if;
exit when c_rec2%notfound;
end loop;
end if;
exit when c_rec%notfound;
end loop;
if flg=1
then
RETURN 1;
else
return 0;
end if;
END;
/
The first cursor of the function is calculating the week number and removing the smallest week and again reorganizing and renumbering the week.
The second cursor is giving all the dates removing saturday and sunday and their respective week numbers.
