This function takes 2 dates as parameter and returns the number of working days. You need to add the list of holidays. (I have added a few as sample)
Code:
CREATE OR REPLACE FUNCTION BUSINESS_DAYS (
i_Date1 IN DATE,
i_Date2 IN DATE
)
RETURN NUMBER
IS
V_LEAST DATE := TRUNC(LEAST(i_Date1, i_Date2));
V_GREATEST DATE := TRUNC(GREATEST(i_Date1, i_Date2));
V_RESULT NUMBER;
V_FIRST_DOWK NUMBER;
V_LAST_DOWK NUMBER;
V_WKS_BTWN NUMBER;
V_SAT_ADJST NUMBER := 0;
V_COUNT_FIRST NUMBER := 1;
V_PLS PLS_INTEGER := 1;
BEGIN
V_FIRST_DOWK := TO_NUMBER( TO_CHAR(V_LEAST, 'D' ) );
V_LAST_DOWK := TO_NUMBER( TO_CHAR(V_GREATEST, 'D' ) );
V_WKS_BTWN := TRUNC( (V_GREATEST - V_LEAST ) / 7 );
IF V_FIRST_DOWK > V_LAST_DOWK
THEN
V_WKS_BTWN := V_WKS_BTWN + 1;
END IF;
IF V_FIRST_DOWK = 7
THEN
V_SAT_ADJST := 1;
V_COUNT_FIRST := 0;
ELSIF V_FIRST_DOWK = 1
THEN
V_COUNT_FIRST := 0;
ELSE
NULL;
END IF;
IF V_LAST_DOWK = 7
THEN
V_SAT_ADJST := V_SAT_ADJST - 1;
END IF;
V_RESULT := ((V_WKS_BTWN * 5 ) + (V_LAST_DOWK - V_FIRST_DOWK + V_COUNT_FIRST) + V_SAT_ADJST) * V_PLS;
IF V_RESULT > 0
THEN
--FOR 2007
--FOR 2008
--FOR 2009
IF TO_DATE('01-01-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
THEN
V_RESULT := V_RESULT - 1;
END IF;
IF TO_DATE('19-03-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
THEN
V_RESULT := V_RESULT - 1;
END IF;
IF TO_DATE('15-07-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
THEN
V_RESULT := V_RESULT - 1;
END IF;
----------------------------------------------------------
------------------add more dates here-----------
----------------------------------------------------------
RETURN V_RESULT;
/*
EXCEPTION
WHEN OTHERS
THEN
V_ERROR := TO_CHAR(SQLERRM);
-- INSERT INTO CHARAN(NAME, Dt)
-- VALUES(V_ERROR || ' - ' || i_Date1 || ' & ' || i_Date2, SYSDATE);
-- COMMIT;
-- RAISE_APPLICATION_ERROR('-20001', 'Oops. Invalid Date format. It should be "DD-MON-YYYY"');
RAISE_APPLICATION_ERROR('-20001', TO_CHAR(SQLERRM) || ' - ' || i_Date1 || ' & ' || i_Date2);
*/
END;