Oracle’s Equivalent for Networkdays Function in Excel, by Zahar Hilkevich

I was recently approached by a colleague at work with a question if I know an Oracle’s Equivalent for Networkdays Function in Excel. I did not have an immediate answer, but a few minutes later I was able to find a way.

First, let’s define what the Networkdays function does. It should give the number of days between two given dates excluding Sundays and Saturdays.

If we had a calendar table (with a work_day column) the problem would be solved fairly easily:

SELECT COUNT(1)
FROM calendar_table
WHERE TO_CHAR(work_day, 'D') BETWEEN 2 AND 6

To ensure that this code works for different geographic regions, it would be safer to add nls_date_language attribute as follows:

SELECT COUNT(1)
FROM calendar_table
WHERE TO_CHAR(work_day, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6

In absence of the calendar_table, we can mimic it using a standard method for a numeric/date range generator. Let say, we need to generate a range of dates between 15-May-2015 and 20-Aug-2015:

SELECT DATE'2015-05-15'+LEVEL-1 AS day
FROM dual
CONNECT BY DATE'2015-05-15'+LEVEL-1 <= DATE'2015-08-20';

Result (partial):
DAY
---------
15-MAY-15
16-MAY-15
17-MAY-15
18-MAY-15
...
17-AUG-15
18-AUG-15
19-AUG-15
20-AUG-15

So after excluding the weekend days, the counting task becomes trivial:

SELECT COUNT(1)
FROM dual
WHERE TO_CHAR(DATE'2015-05-15'+LEVEL-1, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6
CONNECT BY DATE'2015-05-15'+LEVEL-1 <= DATE'2015-08-20';

Result:
COUNT(1)
--------
      70

Finally, we are ready to package it all together and create a function:

CREATE OR REPLACE FUNCTION NETWORKDAYS(p_date1 DATE, p_date2 DATE) RETURN INTEGER
AS
   v_result INTEGER;
BEGIN
  SELECT COUNT(1) INTO v_result
  FROM dual
  WHERE TO_CHAR(p_date1+LEVEL-1, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6
  CONNECT BY TRUNC(p_date1)+LEVEL-1 <= TRUNC(p_date2);

  RETURN v_result;
END;
/

This is a quite elegant solution, though I had a feeling that it is a bit over-complicated. A few minutes later, I derived a non-SQL approach:

CREATE OR REPLACE FUNCTION NETWORKDAYS(p_date1 DATE, p_date2 DATE) RETURN INTEGER
AS
   v_date1 DATE:=CASE TO_CHAR(p_date1, 'D', 'nls_date_language=AMERICAN') 
		      WHEN 1 THEN TRUNC(p_date1)+1
		      WHEN 6 THEN TRUNC(p_date1)+2
		      ELSE TRUNC(p_date1)
		 END;
   v_date2 DATE:=CASE TO_CHAR(p_date2, 'D', 'nls_date_language=AMERICAN') 
		      WHEN 1 THEN TRUNC(p_date2)+1
		      WHEN 6 THEN TRUNC(p_date2)+2
		      ELSE TRUNC(p_date2)
		 END;
BEGIN
  IF p_date1>p_date2 THEN
	RETURN 0;
  ELSIF p_date1=p_date2 THEN
	RETURN 1;
  ELSE
	RETURN (TRUNC(v_date2, 'D') - TRUNC(v_date1, 'D')) * 5/7 +
 	       TO_CHAR(v_date2, 'D', 'nls_date_language=AMERICAN') -
	       TO_CHAR(v_date1, 'D', 'nls_date_language=AMERICAN');
  END IF;
END;
/

A few comments for better understanding the above code:
1) If one(or both) function parameter values is a week-end day, we change it to the following Monday as it does not change the number of non-weekend days in the given range.
2) TRUNC function with ‘D’ argument returns the first day of the week, so we can expect the number of days between two Sundays (or whatever the first day is) to be a multiple of 7, and so 5/7 gives us the number of non-weekend days.
3) TO_CHAR(v_date2, ‘D’, ‘nls_date_language=AMERICAN’) – TO_CHAR(v_date1, ‘D’, ‘nls_date_language=AMERICAN’) – is an adjustment to the result based on the day of the week of each of the date range parameters.

Quick test for the new function – Find number of work days before the end of the year:

SELECT  SYSDATE+LEVEL-1 AS day,
        TO_CHAR(sysdate+level-1, 'DY') AS day_of_week,
        networkdays(sysdate+level-1, date'2015-12-31') netdays
FROM dual
CONNECT BY LEVEL<=15

Result:
DAY       DAY_OF_WEEK     NETDAYS
--------- ------------ ----------
10-OCT-15 SAT                  58
11-OCT-15 SUN                  58
12-OCT-15 MON                  58
13-OCT-15 TUE                  57
14-OCT-15 WED                  56
15-OCT-15 THU                  55
16-OCT-15 FRI                  54
17-OCT-15 SAT                  53
18-OCT-15 SUN                  53
19-OCT-15 MON                  53
20-OCT-15 TUE                  52
21-OCT-15 WED                  51
22-OCT-15 THU                  50
23-OCT-15 FRI                  49
24-OCT-15 SAT                  48

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s