Обсуждение: 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