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
> 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