Re: Calculating # of working days in a period.

ispa_at_beagle.ispa.fsu.edu (ispa) writes:

>I want to calculate the number of working days in a
>period, assuming a work of week of 5 days. It should
>be simple enough, count the number days in the period
>minus the weekends. But, I also want to eliminate
>holidays. Is there a simple solution to this?

You've got to create a table to hold annual holidays - but the rest is just math.

The following sqlplus script returns the number of working days passed for a given period-to-date:

• start of script Rem - start month format is YYYYMM

DEFINE start_month = TO_DATE(&1,'YYYYMM')  

COLUMN holidays NEW_VALUE holidays
SELECT COUNT(*) holidays
  FROM holidays
    WHERE holiday BETWEEN &start_month AND SYSDATE /  

 SELECT &start_month begin_dt, SYSDATE end_date ,

    ((TRUNC(SYSDATE,'D')-TRUNC((&start_month) +6,'D'))*5/7     + MOD(7-TO_NUMBER(TO_CHAR((&start_month),'D')),6)     + LEAST(TO_NUMBER(TO_CHAR(SYSDATE,'D'))-1,5) - &holidays) work_day   FROM dual
/
EXIT work_day  

• end of script

ps - Thanks to Ashok Ganti wherever you are. Received on Tue Nov 01 1994 - 23:50:34 CET