 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,504
# 1
Jun 30 '09
| |
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) -
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;
-
-
|
