| how to retrieve the working days ? [message #79926] |
Fri, 26 July 2002 04:18  |
kat
Messages: 15
Registered: February 2000
|
Junior Member |
|
|
hi everyone!
for an application I need to know the number of workdays for the months (less the statutory holidays and weekends of course). do you have any suggestions how to solve this problem programmatically?
best regards ,
kat
|
|
|
|
| Re: how to retrieve the working days ? [message #79934 is a reply to message #79926] |
Fri, 26 July 2002 07:42  |
Bob H.
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
Something like this could be used to loop from the beginning of a month through the end and exclude the days that you do not wish to count.
declare
dweek varchar2(1);
counter number := 1;
date_calc date;
nbr_days number := 0;
begin
-- create a date variable that indicates the first day of the desired
-- month, however that is obtained, I substituted 07/01/2002
.
.
date_calc := to_date('07/01/2002','mm/dd/yyyy')
--<determine the number of days in the month>
-- if substr(to_char(date_calc,'mm/dd/yyyy'),1,2) in ('01','03','05','07','08','10','12) then
-- nbr_days := 31
.
.
-- Loop through until the number of days is used up
WHILE counter <= nbr_days LOOP
select to_char(date_calc,'d')
into dweek
from dual;
if dweek in ('2','3','4','5','6') then
-- increment the counter if day of week is
-- Monday through Friday
-- <Statuatory Holiday logic could wrap the counter increment>
counter := counter + 1;
end if;
-- add 1 to the date_calc
date_calc := to_date(to_char(to_date(to_number(to_char(date_calc,'j')) + 1,'J'),'mm/dd/yyyy'),'mm/dd/yyyy');
END LOOP;
end;
|
|
|
|
| Re: how to retrieve the working days ? [message #79947 is a reply to message #79926] |
Sat, 27 July 2002 20:40 |
Subhash
Messages: 105
Registered: October 2001
|
Senior Member |
|
|
Try this.
Assumes that Saturday and Friday are ur holidays
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('&L') - to_date('&F')+1 )
where to_char( to_date('&F')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' )
L - Last Day of the Month
F - First Day of the Month
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
