Re: Calculating # of working days in a period.

Ooops, left distribution local, so here it is again.

Here is a procedure we use to add a given number of working days to the current date:

      NAME = workdate
      DEFINITION = <<<
      FUNCTION workdate (start_date IN date, add_days IN number) return date IS
       /* This function adds a number of working days (add_days) */
       /* to a date (start_date) and returns it (end_date)       */
       end_date     date;
       day_name     char(3);
       working_days number(5);
       dummy date;
       CURSOR holiday IS select holiday
                   from dbs.ford_holidays
                   where holiday = end_date;
       BEGIN
       working_days := 0;
       end_date := start_date;
       while (working_days < add_days) LOOP
          end_date := end_date + 1;
          day_name := to_char(end_date,'DY');
          if day_name != 'SAT' and day_name != 'SUN' then
       open holiday;
       fetch holiday into dummy;
       if holiday%NOTFOUND then
          working_days := working_days + 1;
       end if;
       close holiday;
          end if;
       end LOOP;
       return (end_date);
      EXCEPTION when OTHERS then null;
       END;


      >>>

A table holds the holidays, as they change year to year for us. A slight modification to this will allow you to enter the start and end dates and return the number of workdays between. Instead of looping on add_days, loop until end_date and add up the days in the loop.

Dave Jordan Received on Tue Nov 01 1994 - 15:52:08 CET