Caliculating Business Days and adding business days usefull functions for developers

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/