DBA script (3)

From: Paultje Bakker <bakker_at_cs.uq.oz.au>
Date: 21 Feb 1994 01:03:34 GMT
Message-ID: <2k9196$3q3_at_uqcspe.cs.uq.oz.au>

Hi people,

Here's a script for calculating the number of working days (Mo-Fri) between any two dates. Holidays can be included. It's very complicated, but it works. If anyone has a simpler solution, let 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. _at_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('&2') - to_date('25-DEC-93')) *

REM               (to_date('&1') - 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 echo off
set verify off
select floor((to_date('&2')-to_date('&1'))/7)*5

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


                       -trunc(to_date('&1'),'DAY'))


                         )
                     ,1
                     ,(to_date('&2')-7*floor((to_date('&2')-to_date('&1'))/7))


                       -trunc(to_date('&1'),'DAY')


                     ,((to_date('&2')-7*floor((to_date('&2')-to_date('&1'))/7))


                       -trunc(to_date('&1'),'DAY'))-2


                    )
             )
     - decode(to_date('&1')-trunc(to_date('&1'),'DAY'),6,5,7,5,
              to_date('&1')-trunc(to_date('&1'),'DAY'))
     + decode(to_date('&1')-trunc(to_date('&1'),'DAY'),0,0,6,0,7,0,1)

working_days
from sys.dual
where to_date('&2') >= to_date('&1');
set verify on

Rick Bakker, roving DBA

--
Paul Bakker  bakker_at_cs.uq.oz.au | "PhD theses usually marshall an
Computer Science Dept.          | army of facts to starve a
The University of Qld           | slender and tedious truth
QLD 4072 Australia              | into submission" - The Guardian              

Received on Mon Feb 21 1994 - 02:03:34 CET