Caliculating Business Days and adding business days usefull functions for developers

[bujjibabu <mbujjibabu(at)yahoo(dot)com>]

Hi, 

I want to contribute my small amount of effort to the group. 

Please accept this and create a new thread for me, Developers might need these functions to fulfil their busineess needs. 

//create table holidays and insert records of public holidays. 

CREATE TABLE public.holidays ( 
holiday date NULL 
) 
WITHOUT OIDS 
TABLESPACE pg_default 
GO 


select bizdays ( fromdate , todate ) will give actual working days 

//function gives exact no.of working days between from and to date. 

CREATE OR REPLACE FUNCTION public.bizdays (in date, in date) RETURNS int8 AS 
$BODY$ 
SELECT count(*) FROM 
(SELECT extract('dow' FROM $1+x) AS dow 
FROM generate_series(0,$2-$1) x) AS foo 
WHERE dow BETWEEN 1 AND 5; 
$BODY$ 
LANGUAGE 'sql' 
GO 


select fromdate +calcbizdays ( startdate ,noofworkingdays, counter) will get added to no.of.working days 


//function gives exact no.of calendar days after noofworkingdays from startdate , input counter is always zero. 

CREATE OR REPLACE FUNCTION public.calcbizdays (in date, in int4, in int4) RETURNS int4 AS 
$BODY$DECLARE 
currdate ALIAS FOR $1; 
daystoadd ALIAS FOR $2; 
coun ALIAS FOR $3; 
BEGIN 

if (select (bizdays(currdate , 
currdate+daystoadd+coun)- count(*)-1) 
from holidays where holiday between currdate 
and currdate+daystoadd+coun)=daystoadd then 
return daystoadd+coun; 

else 
return addbizdays(currdate,daystoadd,coun+1); 
end if; 
RETURN 0; 
END;$BODY$ 
LANGUAGE 'plpgsql' 
GO 

Thanks, 
Malladi Bujji Babu 





      The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/