Re: removing weekends?

From: H. v. Vroonhoven <hvroon_at_doge.nl>
Date: 1997/03/07
Message-ID: <33208682.386846@news.doge.nl>#1/1

On Wed, 05 Mar 1997 20:10:29 -0500, john.hoskins_at_teldta.com (John Hoskins) wrote:

>Hi,
>I want to subtract two dates, and get the number of 'working' days, in
>other words not count the weekend days. Is their a way to do this in the
>select clause? Or will I need to do it in more than one step.
>Any help would be appreciated,
>John Hoskins
>john.hoskins_at_teldta.com

This will maybe the answer to count working days between to dates. Watch the formats of the dates !

PROCEDURE Working_days                             
       (starting_date DATE,end_date DATE,amount_of_days OUT NUMBER,
        error_code OUT NUMBER) IS                   
BEGIN                                         
   error_code := 0;                                 
   SELECT NVL(                                
          COUNT(*) - SUM(                     
   INSTR(SUBSTR(TO_CHAR(starting_date - 1 +             
   ROWNUM,'DY'),1,1),'S')),0)                 
   INTO amount_of_days FROM table_X                      
   WHERE ROWNUM <= end_date - starting_date;              
   EXCEPTION WHEN NO_DATA_FOUND               
             THEN error_code := 1;                  
             WHEN OTHERS                      
             THEN error_code := 9;                  

END Working_days;

Table_X is a table with a substantial amount of records (at least more

than the maximun amount of working days you'll expect).

Error-code is used at our site because we use this procedure from SQL*Forms 3.0.

Succes.

Hein v. Vroonhoven
(Software engineer Akzo Nobel Information Services Holland) Received on Fri Mar 07 1997 - 00:00:00 CST