Re: Working Days..

From: rob <robvl66_at_hotmail.com>
Date: Thu, 20 Nov 2003 09:14:56 +0100
Message-ID: <bpht64$mep$1@news.tudelft.nl>

Build your own. I use something like this. If the table very big and the days between large I would consider using somthing else then the loop but until then I use:

CREATE OR REPLACE function workdays (p_start DATE,p_end DATE) RETURN NUMBER IS
 counter NUMBER := 0;
BEGIN
   FOR i IN 0..(p_end-p_start)
   LOOP
      IF to_char((p_start + i),'DY') IN ('SUN','SAT')    THEN
      NULL;
   ELSE
      counter := counter + 1;
   END IF;
   END LOOP;
   RETURN counter;
END;
/

SELECT

       REPDATE,CLOSEDDATE,
       WORKDAYS(REPORTDATE,CLOSEDDATE) TOTDAYS,
        CRITICALITY

 FROM ISSUE
 WHERE
        TISSUE = 'Defect'

Regards,
Rob

"Vijays" <srinivasanvijay_2000_at_yahoo.com> schreef in bericht news:fe3d44d1.0311192117.1fe8252d_at_posting.google.com...
> SELECT
> REPDATE,CLOSEDDATE,
> TRUNC(CLOSEDDATE)-TRUNC(REPDATE)+1 TOTDAYS,
> CRITICALITY
> FROM ISSUE
> WHERE
> TISSUE = 'Defect'
>
> This is my select statement..
>
> REPORTEDDATE CLOSEDATE TOTDAYS CRITICALITY
> 05-Feb-02 05-Feb-02 1 High
> 08-Feb-02 08-Feb-02 1 Medium
> 30-Jan-02 09-Feb-02 11 Medium -->i want 9
> 09-Feb-02 09-Feb-02 1 Medium
> 11-Feb-02 11-Feb-02 1 Medium
> 11-Feb-02 11-Feb-02 1 Medium
> 11-Feb-02 11-Feb-02 1 Medium
> 28-Sep-02 28-Sep-02 1 Medium
> 30-Sep-02 30-Sep-02 1 Medium
> 30-Sep-02 03-Oct-02 4 Medium
> 30-Sep-02 03-Oct-02 4 Medium
> 30-Sep-02 30-Sep-02 1 Medium
> 30-Sep-02 04-Oct-02 5 Medium
>
> This is the output...when i get the totdays i wanted to exclude the sat
and
> sun..
>
> Rgds
Received on Thu Nov 20 2003 - 02:14:56 CST