Обсуждение: SQL Challenge: Skip Weekends

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:

Idea #1: Use a reference table
1. Using a script, generate a table of all weekends from 2000 to 2050.
2. Increase the interval by the number of weekends that fall in the relevant
period.

Idea #2:  Some sort of calculation using 5/7 of the interval, adjusted
according to the day of the week of our starting date.  My head hurts trying
to figure this one out.

--
-Josh Berkus

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

here is the algorithm:

date := now - day_of_the_week
interval := interval + day_of_the_week
date := date + int( interval/5)x7 + ( interval mod 5)


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:
> 
> Idea #1: Use a reference table
> 1. Using a script, generate a table of all weekends from 2000 to 2050.
> 2. Increase the interval by the number of weekends that fall in the relevant
> period.
> 
> Idea #2:  Some sort of calculation using 5/7 of the interval, adjusted
> according to the day of the week of our starting date.  My head hurts trying
> to figure this one out.
> 
> --
> -Josh Berkus
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Jean-Luc,

> date := now - day_of_the_week
> interval := interval + day_of_the_week
> date := date + int( interval/5)x7 + ( interval mod 5)

Merci, merci, merci!

-Josh

Joe,

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

Thank you.  Once again, you come to the rescue when I'm stuck.  I'll
try your solution and Jean-Luc's, and see which works better/faster. And report back.

-Josh Berkus