Message-ID: <3D12BA7F.1090209@joeconway.com>
Date: Thu, 20 Jun 2002 22:32:47 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.0.0) Gecko/20020530
MIME-Version: 1.0
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge:  Skip Weekends
References: <200206201713.03801.josh@agliodbs.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Josh Berkus wrote:
 > Folks,
 >
 > Hey, I need to write a date calculation function that calculates the
 > date after a number of *workdays* from a specific date.   I pretty
 > much have the "skip holidays" part nailed down, but I don't have a
 > really good way to skip all weekends in the caluclation.  Here's the
 > ideas I've come up with:

How about this (a bit ugly, but I think it does what you want -- minus 
the holidays, which you said you already have figured out):

create or replace function
   get_future_work_day(timestamp, int)
   returns timestamp as '
select
   case when extract(dow from future_work_date) = 6
             then future_work_date + ''2 days''
        when extract(dow from future_work_date) = 0
             then future_work_date + ''1 day''
        else
             future_work_date
   end
from
   (
      select $1
             + (($2 / 5)::text || '' weeks'')
             + (($2 % 5)::text || '' days'')
             as future_work_date
   ) as t1
' language sql;
CREATE
testslv=# select get_future_work_day('2002-06-20',27);
   get_future_work_day
------------------------
  2002-07-29 00:00:00-07
(1 row)

HTH,
Joe