Re: show all values between X and Y

From: rob <rob_at_dsdelft.nl>
Date: Tue, 12 Sep 2006 17:37:08 +0200
Message-ID: <9133e$4506d426$82a1e120$5360@news1.tudelft.nl>

rob wrote:
> 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

BTW
http://asktom.oracle.com is a great resource for these kind of questions http://asktom.oracle.com/pls/ask/f?p=4950:8:2158858165369002354::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:185012348071

/*
|| holiday_table holds the dates to be excluded from the interval calculation
*/
CREATE TABLE holiday_table
  ( holiday_date DATE )
/*
|| Inserted 12/31/02, 1/1/03, 2/17/03, 5/26/03, 7/4/03, 9/1/03, 11/27/03, || 11/28/03, 12/24/03, 12/25/03, 12/31/03, and 1/1/04 into holiday_table */
CREATE TABLE test_interval
  ( start_date DATE,
    end_date DATE
  )
/*
|| Inserted 500 rows into test_interval, with dates ranging from 12/02 into 5/04
|| Tested intervals ranged from 1 day apart to beyond 1 year apart */
/*
|| The PL/SQL implementation of the business day logic */
CREATE OR REPLACE FUNCTION busdays_between   ( p_start_dt DATE,
    p_end_dt DATE )
RETURN NUMBER
IS

    cln_start_dt DATE        := TRUNC( p_start_dt );
    cln_end_dt   DATE        := TRUNC( p_end_dt );
    first_dt     DATE;
    last_dt      DATE;
    orientation  PLS_INTEGER := 1;
    first_dowk   PLS_INTEGER;
    count_first  PLS_INTEGER := 1;
    last_dowk    PLS_INTEGER;
    wks_btwn     PLS_INTEGER;

    sat_adjst PLS_INTEGER := 0;
    count_hdays PLS_INTEGER := 0;
    return_value NUMBER;
BEGIN
    IF cln_start_dt <= cln_end_dt
    THEN

       first_dt := cln_start_dt;
       last_dt := cln_end_dt;
    ELSE
       first_dt := cln_end_dt;
       last_dt := cln_start_dt;
       orientation := -1;

    END IF;
    SELECT COUNT( * )

      INTO count_hdays
      FROM holiday_table
     WHERE holiday_date BETWEEN first_dt
                            AND last_dt;

    first_dowk := TO_NUMBER( TO_CHAR( first_dt, 'D' ) );     last_dowk := TO_NUMBER( TO_CHAR( last_dt, 'D' ) );     wks_btwn := TRUNC( ( last_dt - first_dt ) / 7 );     IF first_dowk > last_dowk
    THEN
       wks_btwn := wks_btwn + 1;
    END IF;
    IF first_dowk = 7
    THEN

       sat_adjst := 1;
       count_first := 0;

    ELSIF first_dowk = 1
    THEN
       count_first := 0;
    ELSE
       NULL;
    END IF;
    IF last_dowk = 7
    THEN
       sat_adjst := sat_adjst - 1;
    END IF;
    return_value := ( ( wks_btwn * 5 ) +

                      ( last_dowk - first_dowk + count_first ) +
                      sat_adjst - count_hdays
                    ) * orientation;

    RETURN return_value;
END; /* busdays_between */
/*
|| PL/SQL block to loop through the test_interval table and test performance
using
|| discussed method in the thread
*/
DECLARE
    next_interval NUMBER;
    max_interval NUMBER := 0;
BEGIN
    FOR date_rec IN

     ( SELECT *
         FROM test_interval )
    LOOP
       SELECT COUNT( * )
         INTO next_interval
         FROM ( SELECT ROWNUM - 1 rnum
                  FROM ALL_OBJECTS
                 WHERE ROWNUM <= date_rec.end_date - date_rec.start_date + 1
              )
        WHERE TO_CHAR( date_rec.start_date + rnum, 'DY' ) NOT IN ( 
'SAT', 'SUN' )
          AND NOT EXISTS
              ( SELECT NULL
                  FROM holiday_table
                 WHERE holiday_date = TRUNC( date_rec.start_date + rnum )
              );
       max_interval := GREATEST( next_interval, max_interval );

    END LOOP;
    DBMS_OUTPUT.PUT_LINE( TO_CHAR( max_interval ) ); END;
/*
344

Elapsed: 00:00:27.89 */
/*
|| Same basic PL/SQL block as above, using function instead */
DECLARE
    next_interval NUMBER;
    max_interval NUMBER := 0;
BEGIN
    FOR date_rec IN

     ( SELECT *
         FROM test_interval )
    LOOP
       SELECT busdays_between( date_rec.start_date,
                               date_rec.end_date  )
         INTO next_interval
         FROM DUAL;
       max_interval := GREATEST( next_interval, max_interval );

    END LOOP;
    DBMS_OUTPUT.PUT_LINE( TO_CHAR( max_interval ) ); END;
/*
344

Elapsed: 00:00:00.21 */
/*
|| Showing dependence of performance upon interval length */
DECLARE
    /*
    || One day interval
    */

    date1         DATE   := TO_DATE( '030102', 'YYMMDD' );
    date2         DATE   := TO_DATE( '030103', 'YYMMDD' );

    next_interval NUMBER;
BEGIN
    FOR i IN 1..1000
    LOOP

       SELECT busdays_between( date1, date2 )
         INTO next_interval
         FROM DUAL;

    END LOOP;
END;
/* Elapsed: 00:00:00.31 */
DECLARE
    /*
    || One year interval
    */

    date1         DATE   := TO_DATE( '030102', 'YYMMDD' );
    date2         DATE   := TO_DATE( '040102', 'YYMMDD' );

    next_interval NUMBER;
BEGIN
    FOR i IN 1..1000
    LOOP

       SELECT busdays_between( date1, date2 )
         INTO next_interval
         FROM DUAL;

    END LOOP;
END;
/* Elapsed: 00:00:00.41 */
DECLARE
    /*
    || One day interval
    */

    date1         DATE   := TO_DATE( '030102', 'YYMMDD' );
    date2         DATE   := TO_DATE( '030103', 'YYMMDD' );

    next_interval NUMBER;
BEGIN
    FOR i IN 1..1000
    LOOP

       SELECT COUNT( * )
         INTO next_interval
         FROM ( SELECT ROWNUM - 1 rnum
                  FROM ALL_OBJECTS
                 WHERE ROWNUM <= date2 - date1 + 1
              )
        WHERE TO_CHAR( date1 + rnum, 'DY' ) NOT IN ( 'SAT', 'SUN' )
          AND NOT EXISTS
              ( SELECT NULL
                  FROM holiday_table
                 WHERE holiday_date = TRUNC( date1 + rnum )
              );

    END LOOP;
END;
/* Elapsed: 00:00:01.82 */
DECLARE
    /*
    || One year interval
    */

    date1         DATE   := TO_DATE( '030102', 'YYMMDD' );
    date2         DATE   := TO_DATE( '040102', 'YYMMDD' );

    next_interval NUMBER;
BEGIN
    FOR i IN 1..1000
    LOOP

       SELECT COUNT( * )
         INTO next_interval
         FROM ( SELECT ROWNUM - 1 rnum
                  FROM ALL_OBJECTS
                 WHERE ROWNUM <= date2 - date1 + 1
              )
        WHERE TO_CHAR( date1 + rnum, 'DY' ) NOT IN ( 'SAT', 'SUN' )
          AND NOT EXISTS
              ( SELECT NULL
                  FROM holiday_table
                 WHERE holiday_date = TRUNC( date1 + rnum )
              );

    END LOOP;
END;
/* Elapsed: 00:01:85.83 */ Received on Tue Sep 12 2006 - 10:37:08 CDT