Re: show all values between X and Y

From: rob <rob_at_dsdelft.nl>
Date: Tue, 12 Sep 2006 17:27:38 +0200
Message-ID: <adfe6$4506d1e9$82a1e120$10528@news2.tudelft.nl>

Stacy Y wrote:
> Hi there,
>
> I am working on a reporting project where all datefields have been set
> as varchar2 fields, so computations on dates are proving to be quite
> tricky.
>
> I have a create date and a completion date, and I need to find the
> number of days between those two dates, not including off days
> (weekends and holidays). The weekend and holiday dates are stored in a
> separate table, so I can reference back to them (i.e: NOT IN (SELECT *
> FROM TABLE).
>
> Can anyone help me with the way this statement would look? I'm stuck.
> This is the line for what I have so far - I don't know how to
> incorporate that reference into it...
>
> SELECT
> SUBSTR(CAD.AEVEN.XDTS,-16,8)-SUBSTR(CAD.AEVEN.CREATE_DATETIME,-16,8) AS
> ELAPSED_DAYS
>
> Thanks!
> Stacy
>

Hi Stacy,

I think writing this code in pure SQL will result in unreadable code. Create a function like my work_days_between: CREATE OR REPLACE function work_days_between (p_start_string VARCHAR2,p_end_string VARCHAR2)
RETURN NUMBER IS

    counter       NUMBER := 0;
    p_start       DATE;
    p_end         DATE

BEGIN
    p_start := to_date(p_start_string,'DD-MM-YYYY');     p_end := to_date(p_end_string,'DD-MM-YYYY');     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;
/

Call the function:

select work_days_between(XDTS,CREATE_DATETIME) from CAD.AEVEN;

The function is from the top of my mind and only to demonstrate the principle. You probably need to replace this with more efficient/complex code and implement the holiday table logic.

Regards,
Rob Received on Tue Sep 12 2006 - 10:27:38 CDT