Calculate business days in Oracle SQL(no functions or procedure)

I am trying to calculate business days between two dates in Oracle select. I got to the point when my calculation gives most results correct for given dates (I compare it with NETWORKDAYS in excel) but sometimes it varies from 2 days to -2 days - and I don't know why...

Here's my code:

SELECT
((to_char(CompleteDate,'J') - to_char(InstallDate,'J'))+1) - (((to_char(CompleteDate,'WW')+ (52 * ((to_char(CompleteDate,'YYYY') - to_char(InstallDate,'YYYY'))))) - to_char(InstallDate,'WW'))*2) as BusinessDays
FROM TABLE

Thanks!

Solution 1:

The solution, finally:

SELECT OrderNumber, InstallDate, CompleteDate,
  (TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 - 
  ((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
  (CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
  (CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as BusinessDays
FROM Orders
ORDER BY OrderNumber;

Thanks for all your responses !

Solution 2:

I took into account all the different approaches discussed above and came up with a simple query that gives us the number of working days in each month of the year between two dates:

WITH test_data AS ( SELECT TO_DATE('01-JAN-14') AS start_date, TO_DATE('31-DEC-14') AS end_date
FROM dual ), all_dates AS (
SELECT td.start_date, td.end_date, td.start_date + LEVEL-1 as week_day FROM test_data td CONNECT BY td.start_date + LEVEL-1 <= td.end_date) SELECT TO_CHAR(week_day, 'MON'), COUNT(*)
FROM all_dates WHERE to_char(week_day, 'dy', 'nls_date_language=AMERICAN') NOT IN ('sun' , 'sat') GROUP BY TO_CHAR(week_day, 'MON');

Please feel free to modify the query as needed.

Solution 3:

Try this:

with holidays as 
(
select d from (
select minDate + level -1 d
 from (select min(submitDate) minDate, max (completeDate) maxDate
 from t)
 connect by level <= maxDate - mindate + 1) 
 where to_char(d, 'dy', 'nls_date_language=AMERICAN') not in ('sun' , 'sat')
)
select t.OrderNo, t.submitDate, t.completeDate, count(*) businessDays
from t join holidays h on h.d between t.submitDate and t.completeDate
group by t.OrderNo, t.submitDate, t.completeDate
order by orderno

Here is a sqlfiddle demo

Solution 4:

I changed my example to more readable and to return count of bus. days between. I do not know why you need 'J'- Julian format. All it takes is start/Install and end/Complete dates. You will get correct number of days between 2 dates using this. Replace my dates with yours, add NLS if needed...:

 SELECT Count(*) BusDaysBtwn
  FROM
  (
  SELECT TO_DATE('2013-02-18', 'YYYY-MM-DD') + LEVEL-1 InstallDate  -- MON or any other day 
       , TO_DATE('2013-02-25', 'YYYY-MM-DD') CompleteDate           -- MON or any other day
       , TO_CHAR(TO_DATE('2013-02-18', 'YYYY-MM-DD') + LEVEL-1, 'DY') InstallDay   -- day of week
    FROM dual 
  CONNECT BY LEVEL <= (TO_DATE('2013-02-25', 'YYYY-MM-DD') - TO_DATE('2013-02-18', 'YYYY-MM-DD')) -- end_date - start_date 
   )
   WHERE InstallDay NOT IN ('SAT', 'SUN')
  /

  SQL> 5

Solution 5:

I see that marked final solution is not correct always. Suppose, InstallDate is 1st of the month (if falls on Saturday) and CompleteDate is 16th of the month (if falls on Sunday)

In that case, actual Business Days is 10 but the marked query result will give the answer as 12. So, we have to treat this type of cases too, which I used

(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SAT' AND TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SUN' THEN 2 ELSE 0 END

line to handle it.

SELECT OrderNumber, InstallDate, CompleteDate,
(TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 - 
((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SAT' AND TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SUN' THEN 2 ELSE 0 END)as BusinessDays
FROM Orders
ORDER BY OrderNumber;