Re: SQL Challenge: Skip Weekends

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