Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3F8DD633EF5 for ; Fri, 13 Nov 2009 10:35:19 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 41369-01-8 for ; Fri, 13 Nov 2009 14:34:59 +0000 (UTC) X-Greylist: delayed 00:06:40.328566 by SQLgrey-1.7.6 Received: from web95213.mail.in2.yahoo.com (web95213.mail.in2.yahoo.com [203.104.18.189]) by mail.postgresql.org (Postfix) with SMTP id 7438C6352B6 for ; Fri, 13 Nov 2009 10:34:14 -0400 (AST) Received: (qmail 75372 invoked by uid 60001); 13 Nov 2009 14:27:30 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1258122450; bh=AqBM0vMhMeAm2klLwbxkC21Qn9jyh3NZew9CAYtbTKs=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=KVzvWw5GWkwIpmHDfrUKHPKuhEQReDWzD7Fl2icoDa9jyXOSXl3/H33aUW4JF02XP6d0fWvwQNrbBL5u4Uyi/paPtawmgLmDtKuJsrYh1ofwQZ+n87YkxCHBkXrveTozeqT87L432AJcHh2N0tOym8bqRC7S11r262kuXHp633E= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=fP158Wjlx7AV99po2Xv3H5ozyRHyd09iatX1iIXstlIQC3yToxJYvFHpM6uoqqJJgi5obH4Kqrm3dCSUes8E0LZx4tgnhzJyJV3q/QIPEFF9OoTzo0hQeqPBoMWNEu2Ttecgtzyz77mKd7m8lQoz5EBMhW4aQOAzC12L2C8FKz0=; Message-ID: <453289.75353.qm@web95213.mail.in2.yahoo.com> X-YMail-OSG: OvMH2QYVM1kCa60DxFpY.HHjKQrK0WOjrt0ItR6AMoC_elPKo5YiWxOA05wBjpNssIWwaDxwszlzL6HRlh2CecpEpPsEpxgBf0Iedcud3sp2gTzQXyQq4OzkokioR509_9mwxuOjBz9xboak1Z.9QKyXJLtR7ETeOU_Hb.cPitkvXDBxtzrv4Zrl70bqGnoKylJ6io5tWFXYFcuI2r9Hfhntqmljfjo_ND8fgIqZ3hgWNtyg.MIAuuMFDAEwsBKi1Zc1QhFn0gsb99clQbzTaRdlaO0c4mrMYM1LlcIcSIR9WWw8ekDyoMaHZAn6iRF5hwxRB1JjxT2Av1i0sa8sx2VHWw-- Received: from [87.194.169.15] by web95213.mail.in2.yahoo.com via HTTP; Fri, 13 Nov 2009 19:57:29 IST X-Mailer: YahooMailWebService/0.7.361.4 Date: Fri, 13 Nov 2009 19:57:29 +0530 (IST) From: bujjibabu Reply-To: bujjibabu Subject: Caliculating Business Days and adding business days usefull functions for developers To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1477427391-1258122449=:75353" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200911/39 X-Sequence-Number: 33390 --0-1477427391-1258122449=:75353 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable =0A=0AHi, =0A=0AI want to contribute my small amount of effort to the group= . =0A=0APlease accept this and create a new thread for me, Developers might= need these functions to fulfil their busineess needs. =0A=0A//create table= holidays and insert records of public holidays. =0A=0ACREATE TABLE public.= holidays ( =0Aholiday date NULL =0A) =0AWITHOUT OIDS =0ATABLESPACE pg_defau= lt =0AGO =0A=0A=0Aselect bizdays ( fromdate , todate ) will give actual wor= king days =0A=0A//function gives exact no.of working days between from and = to date. =0A=0ACREATE OR REPLACE FUNCTION public.bizdays (in date, in date)= RETURNS int8 AS =0A$BODY$ =0ASELECT count(*) FROM =0A(SELECT extract('dow'= FROM $1+x) AS dow =0AFROM generate_series(0,$2-$1) x) AS foo =0AWHERE dow = BETWEEN 1 AND 5; =0A$BODY$ =0ALANGUAGE 'sql' =0AGO =0A=0A=0Aselect fromdate= +calcbizdays ( startdate ,noofworkingdays, counter) will get added to no.o= f.working days =0A=0A=0A//function gives exact no.of calendar days after no= ofworkingdays from startdate , input counter is always zero. =0A=0ACREATE O= R REPLACE FUNCTION public.calcbizdays (in date, in int4, in int4) RETURNS i= nt4 AS =0A$BODY$DECLARE =0Acurrdate ALIAS FOR $1; =0Adaystoadd ALIAS FOR $2= ; =0Acoun ALIAS FOR $3; =0ABEGIN =0A=0Aif (select (bizdays(currdate , =0Acu= rrdate+daystoadd+coun)- count(*)-1) =0Afrom holidays where holiday between = currdate =0Aand currdate+daystoadd+coun)=3Ddaystoadd then =0Areturn daystoa= dd+coun; =0A=0Aelse =0Areturn addbizdays(currdate,daystoadd,coun+1); =0Aend= if; =0ARETURN 0; =0AEND;$BODY$ =0ALANGUAGE 'plpgsql' =0AGO =0A=0AThanks, = =0AMalladi Bujji Babu =0A=0A=0A=0A=0A=0A The INTERNET now has a person= ality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ --0-1477427391-1258122449=:75353 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <= div style=3D'font-family: Arial; font-size: 10pt; color: #000000'>

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.
<= br>CREATE TABLE public.holidays (
    holiday  = ;  date NULL
    )
WITHOUT OIDS
TABLESPACE p= g_default
GO


select bizdays ( fromdate , todate )  will = give actual working days

//function gives exact no.of working days b= etween from and to date.
=0A
CREATE OR REPLACE FUNCTION public.bizday= s (in date, in date) RETURNS int8 AS
$BODY$
  SELECT count(*) FR= OM
    (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 fr= omdate +calcbizdays ( startdate ,noofworkingdays, counter)  will get a= dded to no.of.working days


//function gives exact no.of calendar= days after noofworkingdays from startdate , input counter is always zero.<= br>
CREATE OR REPLACE FUNCTION public.calcbizdays (in date, in int4, in = int4) RETURNS int4 AS
$BODY$DECLARE
currdate ALIAS FOR $1;
daysto= add ALIAS FOR $2;
coun ALIAS FOR $3;
BEGIN

if (select (bizdays= (currdate ,
currdate+daystoadd+coun)- count(*)-1)
from holidays whe= re holiday between currdate
and  currdate+daystoadd+coun)=3Ddaysto= add then
return daystoadd+coun;

else
return addbizdays(currdate,daystoadd,coun+1= );
end if;
RETURN 0;
END;$BODY$
LANGUAGE 'plpgsql'
GO
Thanks,
Malladi Bujji Babu



=0A=0A=0A=0A =
=0AThe INTERNET now has a personality. YOURS! <= a href=3D"http://in.rd.yahoo.com/tagline_yyi_1/*http://in.yahoo.com/" targe= t=3D"_blank">See your Yahoo! Homepage. --0-1477427391-1258122449=:75353--