Re: Working Days

I wrote this PL/SQL block to calculate the number of weekend days between any two given dates.

The select statement does all the work and the PL/SQL stuff was put for testing purposes only.

There are two parts to the calculation :-

1. full weeks = 2 out of every 7
2. left over days = use a matrix that looks like this

End date --> Sat Sun Mon Tue Wed Thu Fri Start Sun 0 1 1 1 1 1 1
| Mon 1 0 0 0 0 0 0
| Tue 1 2 0 0 0 0 0

  v      Wed  1   2   2   0   0   0   0
         Thu  1   2   2   2   0   0   0
         Fri  1   2   2   2   2   0   0
         Sat  1   2   2   2   2   2   0

e.g., if start date is Wed and end date is Mon, the number of leftover days is 6 of which 2 are weekends and so on.

Enjoy !

Raja

raja_at_crl.com

declare
start_date date;
end_date date;
start_day char(3);
end_day char(3);
regular number(4);
weekends number(5);

--
begin
start_date := '05-DEC-93';
for i in 1..7 loop
end_date := start_date;
for j in 0..14 loop
 end_date := start_date + j;
	select 	to_char(start_date,'DY'),
		to_char(end_date,'DY'),
	       	trunc((1+(end_date - start_date)) / 7)*2,
		decode ( to_char(start_date,'DY'),  
		       'SUN', decode ( to_char(end_date,'DY'), 
       			              'SAT', 0,
				      1
				     ),
			decode ( to_char(end_date,'DY'),  
				'SAT', 1,
				decode ( sign(to_number(
					       to_char(start_date,'d')) -
					      (to_number(
						to_char(end_date,'d')) + 1)),
					+1, 2,
					 0
					)
			       )
		       )  +
	       	      trunc((1+(end_date - start_date)) / 7)*2
		into start_day,
	             end_day,
		     regular,
		     weekends
	from dual;

	insert into raja_temp values (start_date, start_day, end_date, end_day,
				      regular, weekends); end loop; start_date := start_date +1; end
loop; end;
/