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:
CREATE OR REPLACE FUNCTION working_days
SELECT SUM(DECODE(DAY, '7', 0, '6', 0, 1))
(SELECT TO_NUMBER(TO_CHAR(p_start_date + row_num,'D')) AS DAY
(SELECT ROWNUM-1 AS row_num FROM ALL_OBJECTS
WHERE ROWNUM < TRUNC(p_end_date+1) - TRUNC(p_start_date-1)));
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?
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.
Oracle9i Enterprise Edition Release 184.108.40.206.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 220.127.116.11.0 - Production
SQL> SET SERVEROUTPUT ON;
2 FUNCTION named_days_between (
3 vp_min IN DATE,
4 vp_max IN DATE,
5 vp_day IN VARCHAR2)
6 RETURN NUMBER
9 RETURN TRUNC (NEXT_DAY (vp_max, vp_day) -
10 NEXT_DAY (vp_min - 1, vp_day)) / 7;
13 FUNCTION business_days_between (
14 vp_min IN DATE,
15 vp_max IN DATE)
16 RETURN NUMBER
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;
24 DBMS_OUTPUT.PUT_LINE ('Business Days: ' ||
25 business_days_between (SYSDATE, SYSDATE + 15));
Business Days: 12
PL/SQL procedure successfully completed.
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).
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.