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
--

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.

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!

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.

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>

---

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.

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?

Quote:

---

Originally posted by padders
Care to race?

---

Test by urself the above qry in my post vs PL/SQL u designed..

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.

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!

There is the issue that the two methodologies give different results, also ...

Code:

---

alter session set nls_date_format = 'DD-Mon-YYYY Day';

select
  min_dte,
  max_dte,
  working_days(min_dte,max_dte) wd,
  business_days_between(min_dte,max_dte) bdb
from
  table_name
where
  rownum <100 and
  working_days(min_dte,max_dte) != business_days_between(min_dte,max_dte)
/

MIN_DTE              MAX_DTE                      WD        BDB
--------------------- --------------------- ---------- ----------
14-Aug-2002 Wednesday 16-Aug-2002 Friday            2          3
11-Aug-2002 Sunday    13-Aug-2002 Tuesday            3          2
08-Aug-2002 Thursday  10-Aug-2002 Saturday          1          2
04-Aug-2002 Sunday    06-Aug-2002 Tuesday            3          2
03-Aug-2002 Saturday  05-Aug-2002 Monday            2          1
01-Aug-2002 Thursday  03-Aug-2002 Saturday          1          2
31-Jul-2002 Wednesday 02-Aug-2002 Friday            2          3
27-Jul-2002 Saturday  29-Jul-2002 Monday            2          1
24-Jul-2002 Wednesday 26-Jul-2002 Friday            2          3
21-Jul-2002 Sunday    23-Jul-2002 Tuesday            3          2
18-Jul-2002 Thursday  20-Jul-2002 Saturday          1          2

MIN_DTE              MAX_DTE                      WD        BDB
--------------------- --------------------- ---------- ----------
13-Jul-2002 Saturday  15-Jul-2002 Monday            2          1
11-Jul-2002 Thursday  13-Jul-2002 Saturday          1          2
10-Jul-2002 Wednesday 12-Jul-2002 Friday            2          3
07-Jul-2002 Sunday    09-Jul-2002 Tuesday            3          2
03-Jul-2002 Wednesday 05-Jul-2002 Friday            2          3
30-Jun-2002 Sunday    02-Jul-2002 Tuesday            3          2
29-Jun-2002 Saturday  01-Jul-2002 Monday            2          1
27-Jun-2002 Thursday  29-Jun-2002 Saturday          1          2
22-Jun-2002 Saturday  24-Jun-2002 Monday            2          1
19-Jun-2002 Wednesday 21-Jun-2002 Friday            2          3
16-Jun-2002 Sunday    18-Jun-2002 Tuesday            3          2

MIN_DTE              MAX_DTE                      WD        BDB
--------------------- --------------------- ---------- ----------
13-Jun-2002 Thursday  15-Jun-2002 Saturday          1          2
09-Jun-2002 Sunday    11-Jun-2002 Tuesday            3          2
08-Jun-2002 Saturday  10-Jun-2002 Monday            2          1
06-Jun-2002 Thursday  08-Jun-2002 Saturday          1          2
05-Jun-2002 Wednesday 07-Jun-2002 Friday            2          3
01-Jun-2002 Saturday  03-Jun-2002 Monday            2          1
29-May-2002 Wednesday 31-May-2002 Friday            2          3
26-May-2002 Sunday    28-May-2002 Tuesday            3          2
23-May-2002 Thursday  25-May-2002 Saturday          1          2
18-May-2002 Saturday  20-May-2002 Monday            2          1
16-May-2002 Thursday  18-May-2002 Saturday          1          2
...

---

It looks like working_days has a bug.

I note that business_days_between is also inclusive of both min_dte and max_dte, and working_days is not, but that's a diff issue.

Quote:

---

Originally posted by slimdave
[B]There is the issue that the two methodologies give different results, also ...

Code:

---

alter session set nls_date_format = 'DD-Mon-YYYY Day';
...

---

It looks like working_days has a bug.

---

No, not realy. You've got those results because you have different NLS settings than I do. I've specifficaly mentioned that the whole thing depends on the NLS_TERRITORY settings. The numbering of days if the week in America is different than it is in Europe, for example. So if you have NLS_TERRITORY set to "America", you either need to execute another alter session statement to set different territory, e.g.:

alter session set nls_territory = 'UNITED KINGDOM';

or you would have to modify the usage of the DECODE(day, ...) inside the function from

Code:

---

...
  SELECT SUM(DECODE(DAY, '7', 0, '6', 0, 1))
...

---

to

Code:

---

...
  SELECT SUM(DECODE(DAY, '7', 0, '1', 0, 1))
...

---

Padders :

Ur PL/SQL block is much rather vv efficient..!!
:) :)

Abhay.