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

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 BusinessDaysFROM OrdersORDER BY OrderNumber;

Thanks for all your responses !

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.

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(*) businessDaysfrom t join holidays h on h.d between t.submitDate and t.completeDategroup by t.OrderNo, t.submitDate, t.completeDateorder by orderno

Here is a sqlfiddle demo