Skip site navigation (1) Skip section navigation (2)

Caliculating Business Days and adding business days usefull functions for developers

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 (view raw, whole thread or download thread mbox)
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/

pgsql-sql by date

Next:From: Emi LuDate: 2009-11-16 21:16:58
Subject: Fuzzy match under PostgreSQL 8.0.15
Previous:From: Tom LaneDate: 2009-11-11 04:36:50
Subject: Re: ORDB and dot-notation query

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group