469,300 Members | 2,376 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Function To Calculate Business Days

debasisdas
8,127 Expert 4TB
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
0 8615

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by Daniela Nii | last post: by
18 posts views Thread by jimfortune | last post: by
7 posts views Thread by Sam | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.