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/
