From: bujjibabu <mbujjibabu@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Caliculating Business Days and adding business days usefull functions for developers
Date: Fri, 13 Nov 2009 19:57:29 +0530 (IST)
Message-ID: <453289.75353.qm@web95213.mail.in2.yahoo.com> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mbujjibabu@yahoo.com
Subject: Re: Caliculating Business Days and adding business days usefull functions for developers
In-Reply-To: <453289.75353.qm@web95213.mail.in2.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox