DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Number of working Days (Mon-Fri) between two dates?

Hybrid View

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Number of working Days (Mon-Fri) between two dates?

    Has anyone there written - or know of - a query or function to return the number of working days between two dates?

    My pseudo code would be a function accepting the two dates and returning a number of working days. Anyone have anything quicker/more elegant/ simpler?



    IN (start_date, end_date) OUT (Num_days)
    --
    total_days := (end_date - start_date);
    --
    count := 1;
    --
    LOOP UNTIL start_date = end_date
    IF TO_CHAR(start_date,'Dy') IN ('Sat','Sun')
    Then add 1 to count
    start_date := start_date + 1
    END LOOP
    num_days := (total_days - count)
    --
    RETURN num_days
    --
    Last edited by JMac; 02-03-2004 at 02:29 PM.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The following might be considered "more elegant" (that in fact is a matter of taste), but probably not "quicker" as it actualy selects rows from the database. Anyway:
    Code:
    CREATE OR REPLACE FUNCTION working_days
      (p_start_date DATE,
       p_end_date DATE)
    RETURN NUMBER
    IS
      v_result NUMBER;
    BEGIN
      SELECT SUM(DECODE(DAY, '7', 0, '6', 0, 1))
      INTO v_result
      FROM 
      (SELECT TO_NUMBER(TO_CHAR(p_start_date + row_num,'D')) AS DAY
       FROM 
        (SELECT ROWNUM-1 AS row_num FROM ALL_OBJECTS
         WHERE ROWNUM < TRUNC(p_end_date+1) - TRUNC(p_start_date-1)));
      RETURN v_result;
    END;
    Of course the day-of-the-week numbers might be different for Sat-Sun on your system, this depends on your NLS_TERRITORY setting.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    I've boiled my brain reading that one!

    If I could understand how it works I might use it...

    Ice cold Union pilsner for that one!

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    How about this one which will not read any rows..jus a calculation and i suppose will be fastest !!

    Code:
    Select 
      Dayz 
           -
      Nvl(Floor(Round((Dayz+Add_To_Dayz)/7,2)) * 2, 0)
      Week_Days
    From
      (
      Select 
        Trunc(End_Date) 
             - 
        Trunc(Start_Date) 
             - 
        Decode(Substr(To_Char(Start_Date, 'DAY'), 1, 3), 'SUN', 1,
                                                         'SAT', 2,
                                                                0
              ) 
             -
        Decode(Substr(To_Char(End_Date, 'DAY'), 1, 3), 'SUN', 2,
                                                       'SAT', 1,
                                                              0
              ) 
           +
        1
        Dayz, 
        Decode(Substr(To_Char(Start_Date, 'DAY'), 1, 3), 'MON', 1,
                                                         'TUE', 2,
                                                         'WED', 3,
                                                         'THU', 4,
                                                         'FRI', 5,
                                                                0
              ) Add_To_Dayz
      From
      Dual
      )
    ;
    PS : I have tested for few cases, not thoroughly tested...but seems ok to me.

    Abhay.
    Last edited by abhaysk; 02-04-2004 at 07:09 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    As Jurij says this does rather come down to a matter of taste.

    Personally I prefer a non-looping PL/SQL only solution since in my experience that is typically the most efficient. However depending on your requirements it may be equally important that the solution is intuitive so that it can be properly understood and maintained by others - in general people appear to find looping solutions (whether by rows or in PL/SQL) to be more intuitive.
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
    
    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE
      2    FUNCTION named_days_between (
      3      vp_min IN DATE,
      4      vp_max IN DATE,
      5      vp_day IN VARCHAR2)
      6      RETURN NUMBER
      7    IS
      8    BEGIN
      9      RETURN TRUNC (NEXT_DAY (vp_max, vp_day) - 
     10        NEXT_DAY (vp_min - 1, vp_day)) / 7;
     11    END;
     12    
     13    FUNCTION business_days_between (
     14      vp_min IN DATE,
     15      vp_max IN DATE)
     16      RETURN NUMBER
     17    IS
     18    BEGIN
     19      RETURN vp_max - vp_min - (
     20        named_days_between (vp_min, vp_max, 'SAT') + 
     21        named_days_between (vp_min, vp_max, 'SUN')) + 1;
     22    END;
     23  BEGIN
     24    DBMS_OUTPUT.PUT_LINE ('Business Days: ' || 
     25      business_days_between (SYSDATE, SYSDATE + 15));
     26  END;
     27  /
    Business Days: 12
    
    PL/SQL procedure successfully completed.
    
    SQL>

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    when it can be done in a qry, why go for PL/SQL .. do u think its more efficient than SQL? -- most of the times no.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jan 2004
    Posts
    162
    Actually I don't think generalisations like that are particularly helpful. Not that there isn't truth in it but I prefer to test all solutions and implement the most efficient one.

    The example above is trivial to convert to SQL if that is your favourite flavour.
    Code:
    SELECT :vp_max - :vp_min - ((
              TRUNC (NEXT_DAY (:vp_max, 'SAT') - NEXT_DAY (:vp_min - 1, 'SAT')) / 7) + (
              TRUNC (NEXT_DAY (:vp_max, 'SUN') - NEXT_DAY (:vp_min - 1, 'SUN')) / 7)) + 1
    FROM   dual;
    Care to race?

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by padders
    Care to race?
    Test by urself the above qry in my post vs PL/SQL u designed..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    Oh I see what you're getting at. You want to race your SQL statement against my PL/SQL function called from SQL. The cost of context switching between SQL and PL/SQL is no big secret, in fact I would say it is one of the major failings of the integration of SQL and PL/SQL. Obviously if I wanted to go fast I would not call the above function from SQL if that is what you are getting at.

    Nevertheless lets test. Feel free to post any evidence you may have at any time.

    I figure this logic could be used in a range of places in our application, so I will test:

    1. PL/SQL direct assignment. My expression (without function call) vs. your straight SELECT FROM dual, 100,000x in PL/SQL.
    2. PL/SQL calls PL/SQL function. My expression (as function call) vs. your SELECT FROM dual in a function, 100,000x in PL/SQL.
    3. SQL calls PL/SQL function. My expression (as function call) vs. your SELECT FROM dual in a function, 100,000 rows in SQL.
    4. Straight SQL. My SQL expression (in-line view expanded) vs your SQL expression. 100.000 rows in SQL.

    I'll also use a table (called table_name, columns min_dte DATE, max_dte DATE, 1,000,000 rows).
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
    
    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE
      2    v_start_date DATE := SYSDATE;
      3    v_end_date DATE := v_start_date + 15;
      4    v_business_days NUMBER;
      5    v_iterations PLS_INTEGER := 10 ** 5;
      6    v_start_time PLS_INTEGER := 0;
      7    
      8    TYPE tt_business_days IS TABLE OF NUMBER;
      9    t_business_days tt_business_days := tt_business_days ();
     10  BEGIN
     11    DBMS_OUTPUT.PUT_LINE ('[1. PL/SQL direct assignment');
     12    v_start_time := DBMS_UTILITY.get_time;
     13   
     14    FOR i IN 1 .. v_iterations
     15    LOOP
     16      SELECT dayz
     17             - NVL (FLOOR (ROUND ((dayz + add_to_dayz) / 7, 2)) * 2,
     18                0) week_days
     19      INTO   v_business_days
     20      FROM   (SELECT TRUNC (v_end_date) - TRUNC (v_start_date) - 
     21                     DECODE (SUBSTR (TO_CHAR (v_start_date, 'DAY'), 1, 3), 'SUN', 1,'SAT', 2, 0) -
     22                     DECODE (SUBSTR (TO_CHAR (v_end_date, 'DAY'), 1, 3), 'SUN', 2, 'SAT', 1, 0) + 1 dayz,
     23                     DECODE (SUBSTR (TO_CHAR (v_start_date, 'DAY'), 1, 3), 'MON', 1,
     24                      'TUE', 2, 'WED', 3, 'THU', 4, 'FRI', 5, 0) add_to_dayz
     25              FROM   DUAL);
     26    END LOOP;
     27    DBMS_OUTPUT.put_line ('[you (hsecs)] ' || 
     28      (DBMS_UTILITY.get_time - v_start_time));
     29    
     30    v_start_time := DBMS_UTILITY.get_time;
     31    FOR i IN 1 .. v_iterations
     32    LOOP
     33      v_business_days := v_end_date - v_start_date - ((
     34          TRUNC (NEXT_DAY (v_end_date, 'SAT') - NEXT_DAY (v_start_date - 1, 'SAT')) / 7) + (
     35          TRUNC (NEXT_DAY (v_end_date, 'SUN') - NEXT_DAY (v_start_date - 1, 'SUN')) / 7)) + 1;
     36    END LOOP;
     37    DBMS_OUTPUT.put_line ('[me (hsecs)] ' || 
     38      (DBMS_UTILITY.get_time - v_start_time));
     39    
     40    DBMS_OUTPUT.PUT_LINE (CHR (10) || '[2. PL/SQL calls PL/SQL function]');
     41    v_start_time := DBMS_UTILITY.get_time;
     42  
     43    FOR i IN 1 .. v_iterations
     44    LOOP
     45      v_business_days := working_days (v_start_date, v_end_date);
     46    END LOOP;
     47  
     48    DBMS_OUTPUT.put_line ('[you (hsecs)] ' || 
     49      (DBMS_UTILITY.get_time - v_start_time));
     50    
     51    v_start_time := DBMS_UTILITY.get_time;
     52  
     53    FOR i IN 1 .. v_iterations
     54    LOOP
     55      v_business_days := business_days_between (v_start_date, v_end_date);
     56    END LOOP;
     57    
     58    DBMS_OUTPUT.put_line ('[me (hsecs)] ' || 
     59      (DBMS_UTILITY.get_time - v_start_time));
     60    
     61    DBMS_OUTPUT.PUT_LINE (CHR (10) || '[3. SQL calls PL/SQL function]');
     62    t_business_days.DELETE;
     63    v_start_time := DBMS_UTILITY.get_time;
     64    
     65    SELECT working_days (min_dte, max_dte)
     66    BULK COLLECT INTO t_business_days
     67    FROM   table_name
     68    WHERE  ROWNUM <= 100000; 
     69    
     70    DBMS_OUTPUT.put_line ('[you (hsecs)] ' || 
     71      (DBMS_UTILITY.get_time - v_start_time));
     72   
     73    t_business_days.DELETE;
     74    v_start_time := DBMS_UTILITY.get_time;
     75    
     76    SELECT business_days_between (min_dte, max_dte)
     77    BULK COLLECT INTO t_business_days
     78    FROM   table_name
     79    WHERE  ROWNUM <= 100000; 
     80    
     81    DBMS_OUTPUT.put_line ('[me (hsecs)] ' || 
     82      (DBMS_UTILITY.get_time - v_start_time));
     83   
     84    DBMS_OUTPUT.PUT_LINE (CHR (10) || '[4. Straight SQL]');
     85    t_business_days.DELETE;
     86    v_start_time := DBMS_UTILITY.get_time;
     87    
     88    SELECT TRUNC (max_dte) - TRUNC (min_dte) - DECODE (SUBSTR (TO_CHAR (
     89             min_dte, 'DAY'), 1, 3), 'SUN', 1, 'SAT', 2, 0) - DECODE (
     90               SUBSTR (TO_CHAR (max_dte, 'DAY'), 1, 3), 'SUN', 2, 'SAT', 1, 0) + 1 - 
     91           NVL (FLOOR (ROUND ((TRUNC (max_dte) - TRUNC (min_dte) - DECODE (
     92             SUBSTR (TO_CHAR (min_dte, 'DAY'), 1, 3), 'SUN', 1, 'SAT', 2, 0) - 
     93           DECODE (SUBSTR (TO_CHAR (max_dte, 'DAY'), 1, 3), 'SUN', 2, 'SAT', 1, 0) + 
     94            1 + DECODE (SUBSTR (TO_CHAR (min_dte, 'DAY'), 1, 3), 
     95             'MON', 1, 'TUE', 2, 'WED', 3, 'THU', 4, 'FRI', 5, 0)) / 7, 2)) * 2, 0) week_days
     96    BULK COLLECT INTO t_business_days
     97    FROM   table_name
     98    WHERE  ROWNUM <= 100000; 
     99    
    100    DBMS_OUTPUT.put_line ('[you  (hsecs)] ' || (DBMS_UTILITY.get_time - v_start_time));
    101   
    102    t_business_days.DELETE;
    103    v_start_time := DBMS_UTILITY.get_time;
    104    
    105    SELECT max_dte - min_dte - (( 
    106           TRUNC (NEXT_DAY (max_dte, 'SAT') - 
    107             NEXT_DAY (min_dte - 1, 'SAT')) / 7) + ( 
    108           TRUNC (NEXT_DAY (max_dte, 'SUN') - 
    109             NEXT_DAY (min_dte - 1, 'SUN')) / 7)) + 1 business_days 
    110    BULK COLLECT INTO t_business_days
    111    FROM   table_name
    112    WHERE  ROWNUM <= 100000; 
    113    
    114    DBMS_OUTPUT.put_line ('[me (hsecs)] ' || 
    115      (DBMS_UTILITY.get_time - v_start_time));
    116  END;
    117  
    118  /
    [1. PL/SQL direct assignment]
    [you (hsecs)] 2904
    [me (hsecs)] 525
    
    [2. PL/SQL calls PL/SQL function]
    [you (hsecs)] 3043
    [me (hsecs)] 652
    
    [3. SQL calls PL/SQL function]
    [you (hsecs)] 3572
    [me (hsecs)] 871
    
    [4. Straight SQL]
    [you  (hsecs)] 699
    [me (hsecs)] 200
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Looks like your 699 only beat one of my scores - that of calling PL/SQL function in PL/SQL. The cost of context switches from SQL to PL/SQL is IMHO a major limitation of the integration of PL/SQL with SQL. That's a big shame because it would be nice to encapsulate business logic in one function and use it throughout the application. Still, if I need to go really fast in SQL I have a version for that too.

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Now that's what I call an analytical aproach! Simple, without big words, only thing that conts is hard facts (i.e. simple and meaningfull tests with easy to understand results).

    Kudos!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width