Re: SQL Challenge: Skip Weekends

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>
reallygood 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(dowfrom 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'')
asfuture_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