Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Cool Unanswered: Challenge, Oracle x MSSQL

    I am looking for a query to calculate a number of bussiness days between two dates without loops or special huge tables.

    I have found these links

    http://www.oracledba.co.uk/tips/business_days.htm
    http://dbforums.com/t692935.html

    The first solution needs a table with thousands of records, the second one hundereds of loops per one record.

    What is is the best way how to solve it in ORACLE?
    Are you able to solve it in a real SQL code, i.e. in one select like in MSSQL?

    Code:
    /* MSSQL code */
    declare @now datetime set @now='20030602'
    select 
    cast(date as datetime) as "From"
    ,@now                  as "To"
    ,5*((datediff(day,
     dateadd (day, -((datepart(dw,date)+@@DATEFIRST-2)%7),date)
    ,dateadd (day,6-((datepart(dw,@now)+@@DATEFIRST-2)%7),@now)
    )+1)/7)
    -case                ((datepart(dw,date)+@@DATEFIRST-2)%7+1)
    when 1 then 0
    when 2 then 1
    when 3 then 2
    when 4 then 3
    when 5 then 4
    when 6 then 5
    when 7 then 5 end
    -case                ((datepart(dw,@now)+@@DATEFIRST-2)%7+1)
    when 1 then 4
    when 2 then 3 
    when 3 then 2 
    when 4 then 1
    when 5 then 0
    when 6 then 0 
    when 7 then 0 end as "Bussiness days"
    from
    (
              select '20030502' as date
    union all select '20030503'
    union all select '20030504'
    union all select '20030505'
    union all select '20030506'
    union all select '20030507'
    union all select '20030508'
    union all select '20030509'
    ) as x
    
    
    
    /*
     a derived table is used
     date<=@now
    
     ((datepart(dw,date)+@@DATEFIRST-2)%7) is a deterministic version of ((datepart(dw,date)-1)
     ( MO=0,TU=1,...,SA=5,SU=6 )
    
     dateadd (day,number,date) adds number of days to date
    
     The algorithm is very easy. I round dates to limits of weeks, calculate a difference and I deduct not affected bussiness days
     from both the first week and last one.
    
    '20030505'<-('20030509','20030603')->'20030608'
    '20030505'<-     5 weeks=25 BD     ->'20030608'
    ('20030505','20030509') ('20030603','20030608')
       diff 4 BD                       diff 3 BD   
    
    R=25-4-3= 18 BD
    ========================================== 
    */
    Best regards, Ivo Spaleny.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Challenge, Oracle x MSSQL

    Originally posted by ispaleny
    I am looking for a query to calculate a number of bussiness days between two dates without loops or special huge tables.

    I have found these links

    http://www.oracledba.co.uk/tips/business_days.htm
    http://dbforums.com/t692935.html

    The first solution needs a table with thousands of records, the second one hundereds of loops per one record.
    If you follow your second link above through you will find a solution I posted that uses neither large tables nor loops with 100s of iterations.

    I'll post it again here anyway (probably more up to date).

    Code:
    CREATE OR REPLACE PACKAGE bdays_pkg IS
    
      /*
      || Package for calculating with "business days", i.e. counting only weekdays (Mon-Fri)
      */
    
      FUNCTION bdays( p_d1 DATE, p_d2 DATE ) RETURN NUMBER;
      /*
      || Returns number of business days between p_d1 and p_d2
      || Note: this figure is EXCLUSIVE, i.e. bdays( sysdate-1, sysdate ) = 1
      */
    
    END;
    /
    
    CREATE OR REPLACE PACKAGE BODY bdays_pkg IS
    
      /*
      || These constants are defined in a way that is independent of how days are numbered
      || in the particular database (since this can vary from country to country).
      || Of course, it does assume that Saturdays and Sundays are the weekend days!
      */
      k_weekend_day1 CONSTANT INTEGER := TO_CHAR( TO_DATE('04/01/2003','DD/MM/YYYY'),'D');
      k_weekend_day2 CONSTANT INTEGER := TO_CHAR( TO_DATE('05/01/2003','DD/MM/YYYY'),'D');
    
      FUNCTION bdays( p_d1 DATE, p_d2 DATE ) RETURN NUMBER
      IS
        v_d1             DATE    := TRUNC( LEAST(p_d1,p_d2) );        /* Ignore time portion */
        v_d2             DATE    := TRUNC( GREATEST(p_d1,p_d2) );     /* Ignore time portion */
        v_num_full_weeks INTEGER := TRUNC((v_d2-v_d1)/7);             /* Number of full 7-day weeks in period */
        v_num_odd_days   INTEGER := (v_d2-v_d1) - v_num_full_weeks*7; /* Number of odd days */
        v_bdays          INTEGER := v_num_full_weeks*5;               /* Number of business days
                                                                         - initially ignoring odd days */
      BEGIN
        /* Account for odd days */
        FOR i IN 1..v_num_odd_days LOOP
          IF TO_CHAR( v_d1-1+i, 'D') NOT IN (k_weekend_day1,k_weekend_day2) THEN
            v_bdays := v_bdays+1;
          END IF;
        END LOOP;
        IF p_d1 > p_d2 THEN
          v_bdays := -v_bdays;
        END IF;
        RETURN v_bdays;
      END;
    
    END;
    /
    The loop here will never iterate more than 4 times.

    I also have a "pure SQL" solution that looks like this:

    Code:
    SELECT
    FLOOR( ABS(TRUNC(date2)-TRUNC(date1))/7)*5 
    + NVL(LENGTH(TRANSLATE( SUBSTR( '1234567123456'
                                  , TO_NUMBER(TO_CHAR(TRUNC( LEAST(date1,date2) ),'D'))
                                  , MOD(FLOOR(ABS(TRUNC(date1)-TRUNC(date2))),7)
                                  )
                          , '671'
                          , '6'
                          )
                )
         ,0
         ) business_days
    FROM tablename;

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112
    hello,

    these query return number of business days beetween two date without loops :


    select
    '&frdate' From_Date
    ,'&todate' To_Date,
    1 + to_date('&todate') - to_date('&frdate') -
    ((TRUNC(to_date('&todate'),'D') - TRUNC(to_date('&frdate'),'D'))/7)*2
    + DECODE(to_char(to_date('&todate'),'D'),7,-1,0)
    + DECODE(to_char(to_date('&frdate'),'D'),1,-1,0) Business_Days
    from dual

    Hope this will help

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    You can also use these code snippets to calculate the number of business days between two dates:

    Click Here


    Hope that helps,

    clio_usa - OCP - DBA

    dbaclick.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •