From: | bujjibabu <mbujjibabu(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Caliculating Business Days and adding business days usefull functions for developers |
Date: | 2009-11-13 14:27:29 |
Message-ID: | 453289.75353.qm@web95213.mail.in2.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Emi Lu | 2009-11-16 21:16:58 | Fuzzy match under PostgreSQL 8.0.15 |
Previous Message | Tom Lane | 2009-11-11 04:36:50 | Re: ORDB and dot-notation query |