Re: Calculating "working days" for a given period

In article <33311E44.6C87_at_citicorp.com>,   andy.smith_at_citicorp.com wrote:
> Appreciate if anyone has a script that will calculate the number
> of working days, given a starting and ending date.
>
> Both starting and ending date is stored as a number of seconds since
> 1st June 1970.
>
> e.g
>
> TABLE
> -----
> Startdate NUMBER(15)
> Enddate NUMBER(15)

Here's something that will work:

--
-- The following is a SQL Plus script that calculates the number of
-- working days between two dates.  It is the total number of
-- days minus all Saturdays and Sundays.  If the start and end dates
-- are equal, 1 workday is returned (unless date is Saturday or Sunday).
-- Value returned is incorrect if EndDate is less than StartDate.
-- This does not address holidays.
--
-- Steve Cosner stevec_at_zimmer.csufresno.edu
-- http://members.aol.com/stevec5088
--
------------------------------------------------------------------------

Variable T Varchar2(100);
Declare
  StartDate Number := 846547200; --Seconds since 01-Jun-1970 = 29-Mar-97
  EndDate   Number := 847238400; --                          = 04-Apr-97
  Refd  Date  :=  To_Date('01-Jun-1970','DD-MON-YYYY');
  StDt  Date;     -- Start date
  EnDt  Date;     -- End date
  Tot   Integer;  -- Total days, including weekends
  P     Integer;  -- Partial week
  D     Integer;  -- Day number:  M=1, T=2... Sat=6, Sun=7
  F     Integer;  -- Weekend factor (# of days in P which are Sat or Sun
  Btwn  Integer;  -- Weekdays between the two dates--includes both
                  -- first and last day.  If first=last, btwn=1.
Begin
  -- Convert number of seconds to dates --
  StDt:= Refd + Trunc(StartDate/86400);
  EnDt:= Refd + Trunc(EndDate/86400);
  -- Get total number of days --
  Tot := EnDt - StDt + 1;
  -- Adjust for Partial week, and subtract Sat. and Sun.
  P   := Mod(Tot,7);
  D   := To_Number(To_Char(StDt,'D'));
  D   := D-1+7*(1-Least(1,D-1));
  F   := Greatest(Least(0,Greatest(-2,6-D-P)),-Mod(D,7)-1);
  -- Do the final calculation:  5 times # of weeks,
  -- plus the odd days (partial week), except for Sat. and Sun. if they
  -- are included in the partial week.
  Btwn:= 5*Trunc(Tot/7)+Greatest(0,P+F);
  -- Display the result
  :T := 'There are '||To_Char(Btwn)||' working days between '
        ||To_Char(StDt,'Dy, DD-Mon-YYYY')
        ||', and '||To_Char(EnDt,'Dy, DD-Mon-YYYY'||'.');
End;
 .
/
print t

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet