Re: Working Days

In article <1994Jan26.161433.1_at_aa.wl.com> Virtual Citizen Dowswell, dowswea_at_aa.wl.com writes:
> 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).

If you want to be really clever, you can create and fill a 366x366 (including Feb 29) table that contains the holiday count. That way your program need not loop each time it needs the answer - a single one-row select could get it.

CREATE TABLE HOLIDAYS_BETWEEN (
  START_DATE DATE NOT NULL,
  END_DATE DATE NOT NULL,
  NUMBER_OF_HOLIDAYS NUMBER NOT NULL); Fill it using the algorithm that Andrew suggests or something similar. You'll of course need to update this table when your holiday table changes. It could be nicely done with data integrity RDBMS triggers.

Of course, you'll still need to do the other stuff, but that too will require only one database hit (or you can use a nested DECODE). Received on Thu Jan 27 1994 - 23:15:27 CET