Re: Calculating "working days" for a given period

From: Darren Mallette <darren_at_mallette.com>
Date: 1997/03/21
Message-ID: <3332d77f.9359580@nntp.netcom.ca>#1/1

Andy Smith <andy.smith_at_citicorp.com> wrote:
>--
>Hi
>
>Need some help on this one.
>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)
>
>

The following script was posted in c.d.o a few years ago. You'll need to modify it to accept the date format you have (ie. 01-JUN-70 + (startdate/86400)) to get the startdate, etc...

rem From: bakker_at_cs.uq.oz.au (Paultje Bakker) rem Newsgroups: comp.databases.oracle
rem Subject: DBA script (3)
rem Date: 21 Feb 1994 01:03:34 GMT

rem Hi people,

rem Here's a script for calculating the number of working days (Mo-Fri)
rem between any two dates. Holidays can be included. It's very rem complicated, but it works. If anyone has a simpler solution, let rem me know.
REM WORKDAYS.SQL
REM Created 16-NOV-93 by R. Bakker
REM
REM Calculates (inclusive) number of working days (Mo-Fr) between two dates.
REM The two parameters are the two dates. The algorithm below assumes REM the second date is not before the first date; if it is, then results
REM are unpredictable, hence a WHERE clause has been added to select REM nothing in that case.
REM
REM Query started with eg. @workdays '12-NOV-93' '23-DEC-93' REM
REM Method:
REM If the dates are D1 and D2, then first calculate the number of full
REM weeks (ie., seven-day periods) between D1 and D2 and multiply by 5 to
REM get the working days.
REM Further calculations are done with D3, which can be seen as the REM "projection" of D2 onto D1's week; it is always the same day of the
REM week as D3 and always falls within 7 days of D1. REM The number of working days between D3 and D1 is calculated as the REM difference in number of working days between each of them and the REM Sunday preceding D1 [trunc(D1,'DAY)], call it D0. There can only ever
REM be one weekend between D0 and D3 (D1 can at most be the Saturday REM after D0, in which case D3 can at most be the following Friday), REM so if D3-D0 is less than 6 then #working days between them is D3-D0;
REM if D3-D0 is 6 or 7 then #working days is 5, and if D3-D0 > 7 then REM #working days is (D3-D0)-2.
REM Subsequently, subtract the number of working days between D1 and D0 -
REM reduce to 5 if the result is 6 (ie. D1 is a Saturday). REM Finally, add 1 to the result if D1 is a working day. REM
REM This method takes no account of public holidays. This can be done by
REM adding on the line before "working_days" e.g. (one for each public REM holiday!)
REM
REM - decode(sign((to_date('&&last') - to_date('25-DEC-93')) *

REM               (to_date('&&first') - to_date('25-DEC-93'))
REM              )
REM          ,1,0,

REM
decode(to_date('25-DEC-93')-trunc(to_date('25-DEC-93'),'DAY')

REM                  ,0,0,6,0,7,0,1
REM                 )
REM         )

REM which first decides if the date is between the two parameters by REM multiplying the differences and seeing if the result is negative REM (ie. one date is after and one is before the public holiday) or REM zero (the public holiday IS one of the parameters), and if it REM is, then subtract either 1 or 0 from the result, depending on REM how far the public holiday is from its previous Sunday. REM
REM
SET VERIFY OFF;
SET NEWPAGE 1;
SET TIMING OFF;
set echo off
accept first prompt 'Enter the first date (DD-MON-YY): ' ACCEPT LAST PROMPT 'Enter the last date (DD-MON-YY), <CR> for today: '

COLUMN AB NEW_VALUE LAST NOPRINT
SELECT NVL('&&LAST', TO_CHAR(SYSDATE, 'DD-MON-YY')) AB FROM DUAL; select floor((to_date('&&last')-to_date('&&first'))/7)*5

     +
decode((to_date('&&last')-7*floor((to_date('&&last')-to_date('&&first'))/7))

              -trunc(to_date('&&first'),'DAY'),6,5,7,5,
              decode(sign(6

-((to_date('&&last')-7*floor((to_date('&&last')-to_date('&&first'))/7))
                       -trunc(to_date('&&first'),'DAY'))
                         )
                     ,1

,(to_date('&&last')-7*floor((to_date('&&last')-to_date('&&first'))/7))
                       -trunc(to_date('&&first'),'DAY')

,((to_date('&&last')-7*floor((to_date('&&last')-to_date('&&first'))/7))
                       -trunc(to_date('&&first'),'DAY'))-2
                    )
             )
     -
decode(to_date('&&first')-trunc(to_date('&&first'),'DAY'),6,5,7,5,
              to_date('&&first')-trunc(to_date('&&first'),'DAY'))
     +

decode(to_date('&&first')-trunc(to_date('&&first'),'DAY'),0,0,6,0,7,0,1) working_days
from sys.dual
where to_date('&&last') >= to_date('&&first'); set verify on
exit

Darren Mallette
www.geocities.com/HotSprings/7490 Received on Fri Mar 21 1997 - 00:00:00 CST