Re: Working Days

Gary Eckhardt was just kinda wondering about:
>
> Has anyone ever written a Oracle trigger or PL/SQL to compute the number of
> *Working Days* between two dates? Working days, meaning, the days between
> two dates, minus weekends, minus scheduled holidays? We are currently
> working on a solution, but I thought I would troll the net for any ideas
> anyone had on the subject.
> Thanks for any info.
>

I did something like this a few years back at Ford. I might be able to find the program but, I don't have the code handy now (it was in Fortran anyways), but the concept should be easily transferable.

  By subtracting the start date from the end date you will get the total number of days between (OK not exactly rocket science yet, but I'm just warming up).   Since Oracle does not know about holidays and such, I would guess that you will be specifing your holidays in another table. You count the number of occurances in that table that fall between the two dates and subtract that from the total days (Once again - simple stuff).   To handle the weekends you will first need to find out how many times 7 factors into the original total days. This figure is then multiplied by 2 and subtracted.
  Now for the tricky part - what about the days left over, how do I find out how many of them are weekend days? Use the function to_char(startdate,'d') to return the number of the day of the week that you started on (SUN=1,MON=2.... SAT=7) use that and the number of days left over as an index to the array below. That will give you the number of days in the "days left over" that are weekend days. (BTW you could add another column to take care of 0 days left over but since it would be all zeros I left it out).

                # of leftover days
               1   2   3   4   5   6
             |-----------------------|
      SUN(1) | 0 | 0 | 0 | 0 | 0 | 1 |
day   MON(2) | 0 | 0 | 0 | 0 | 1 | 2 |     ex: startdate is THU and I have 4
of    TUE(3) | 0 | 0 | 0 | 1 | 2 | 2 |         days left over.  Element (4,5)
the   WED(4) | 0 | 0 | 1 | 2 | 2 | 2 |         is 2 (fri,SAT,SUN,mon).
week  THU(5) | 0 | 1 | 2 | 2 | 2 | 2 |
      FRI(6) | 1 | 2 | 2 | 2 | 2 | 2 |
      SAT(7) | 1 | 1 | 1 | 1 | 1 | 1 |
             |-----------------------|

(and I thought I would never use Matrix Algebra after college)

Hope this helps,

Andy

-- 
 /---------------------------------------------------------------------------\
 | Andrew Dowswell     | "...They bring me numbness in their bright needles, |
 | Parke-Davis (CPO)   |  they bring me sleep. Now I have lost myself..."    |
 | dowswea_at_aa.wl.com   |                                                     |
 | (313) 996-1337      |             Sylvia Plath "Tulips"                   |
 \---------------------------------------------------------------------------/
 Disclaimer: The opinions of Parke-Davis are not necessarily my own, they
             should not be construed as mine and no flames should be directed 
             at me for something Parke-Davis has said or done.