By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,830 Members | 1,746 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Function To Calculate Business Days

debasisdas
Expert 5K+
P: 8,127
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)


Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION BUSINESS_DAYS (
  2.   i_Date1 IN DATE,
  3.   i_Date2 IN DATE
  4.   )
  5. RETURN NUMBER
  6. IS
  7.   V_LEAST          DATE := TRUNC(LEAST(i_Date1, i_Date2));
  8.   V_GREATEST       DATE := TRUNC(GREATEST(i_Date1, i_Date2));
  9.   V_RESULT         NUMBER;
  10.  
  11.   V_FIRST_DOWK     NUMBER;
  12.   V_LAST_DOWK      NUMBER;
  13.   V_WKS_BTWN       NUMBER;
  14.   V_SAT_ADJST      NUMBER := 0;
  15.   V_COUNT_FIRST    NUMBER := 1;
  16.   V_PLS            PLS_INTEGER := 1;
  17. BEGIN
  18.  
  19.   V_FIRST_DOWK := TO_NUMBER( TO_CHAR(V_LEAST, 'D' ) );
  20.   V_LAST_DOWK := TO_NUMBER( TO_CHAR(V_GREATEST, 'D' ) );
  21.   V_WKS_BTWN := TRUNC( (V_GREATEST - V_LEAST ) / 7 );
  22.  
  23.   IF V_FIRST_DOWK > V_LAST_DOWK
  24.   THEN
  25.      V_WKS_BTWN := V_WKS_BTWN + 1;
  26.   END IF;
  27.  
  28.   IF V_FIRST_DOWK = 7
  29.   THEN
  30.     V_SAT_ADJST := 1;
  31.     V_COUNT_FIRST := 0;
  32.   ELSIF V_FIRST_DOWK = 1
  33.   THEN
  34.     V_COUNT_FIRST := 0;
  35.   ELSE
  36.      NULL;
  37.   END IF;
  38.  
  39.   IF V_LAST_DOWK = 7
  40.   THEN
  41.      V_SAT_ADJST := V_SAT_ADJST - 1;
  42.   END IF;
  43.  
  44.   V_RESULT := ((V_WKS_BTWN * 5 ) + (V_LAST_DOWK - V_FIRST_DOWK + V_COUNT_FIRST) + V_SAT_ADJST) * V_PLS;
  45.  
  46.   IF V_RESULT > 0
  47.   THEN
  48.  --FOR 2007 
  49.  
  50.  --FOR 2008 
  51.  
  52.  --FOR 2009
  53.  
  54.  IF TO_DATE('01-01-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
  55.     THEN
  56.       V_RESULT := V_RESULT - 1;
  57.     END IF;
  58.  
  59.  IF TO_DATE('19-03-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
  60.     THEN
  61.       V_RESULT := V_RESULT - 1;
  62.     END IF;
  63.  
  64.  IF TO_DATE('15-07-2009','DD-MM-YYYY') BETWEEN i_Date1 AND i_Date2
  65.     THEN
  66.       V_RESULT := V_RESULT - 1;
  67.     END IF;
  68. ----------------------------------------------------------
  69. ------------------add more dates here-----------
  70. ----------------------------------------------------------
  71.  
  72.   RETURN V_RESULT;
  73. /*
  74. EXCEPTION
  75.   WHEN OTHERS
  76.   THEN
  77.     V_ERROR := TO_CHAR(SQLERRM);
  78. --    INSERT INTO CHARAN(NAME, Dt)
  79. --    VALUES(V_ERROR || ' - ' || i_Date1 || ' & ' || i_Date2, SYSDATE);
  80. --    COMMIT;
  81. --    RAISE_APPLICATION_ERROR('-20001', 'Oops. Invalid Date format. It should be "DD-MON-YYYY"');
  82.     RAISE_APPLICATION_ERROR('-20001', TO_CHAR(SQLERRM) || ' - ' || i_Date1 || ' & ' || i_Date2);
  83. */
  84. END;
  85.  
  86.  
Jun 30 '09 #1
Share this Article
Share on Google+