Handle Business Days

Introduction
In real business life we consider days as business days only, we exclude holidays and weekends from our calendar days.
So today I will produce solution of how to work with business days.

At first public holidays are different from country to others so I will create table to store every public holidays.

 CREATE TABLE HOLIDAYS (HOLIDAY_DATE DATE, DESCR VARCHAR2 (100));  
   
 ALTER TABLE HOLIDAYS ADD (  
  CONSTRAINT HOLIDAYS_PK  
  PRIMARY KEY  
  (HOLIDAY_DATE));  

I should get weekends days also so I will get using below query
Note that at my country weekend is Fridays and Saturday.

   SELECT TO_DATE ('05-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  
 UNION 
   SELECT TO_DATE ('06-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  

The previous query contains whole weekends from 5th January 1900 to 18th August 2091.

Now I will create database view for whole holidays( Public holidays and weekends)

 CREATE OR REPLACE VIEW HOLIDAYS_VIEW  
 (HOLIDAY_DATE)  
 AS   
 SELECT HOLIDAY_DATE FROM HOLIDAYS  
 UNION  
   SELECT TO_DATE ('05-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  
 UNION  
   SELECT TO_DATE ('06-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999;  
   

Let's now create a package for handling business days which contains below functions

a- ADD_BUSINESSDAYS (IN_DATE DATE, IN_COUNT_DAYS NUMBER)

    Add number of workdays (n) to date (d ) and returns the specific date.

b- SUBTRACT_BUSINESSDAYS (IN_DATE DATE, IN_COUNT_DAYS NUMBER)

    Subtract number of workdays ( n ) from date ( d ) and returns the specific date.

c- DAYS_DIFF (IN_FROM_DATE DATE, IN_TO_DATE DATE)

    Return the number of workdays between date ( d1 ) and date ( d2 ).

Package Specification

 CREATE OR REPLACE PACKAGE MAHMOUD_BUSINESS_DAYS  
 AS  
   FUNCTION ADD_BUSINESSDAYS (IN_DATE DATE, IN_COUNT_DAYS NUMBER)  
    RETURN DATE;  
   
   FUNCTION SUBTRACT_BUSINESSDAYS (IN_DATE DATE, IN_COUNT_DAYS NUMBER)  
    RETURN DATE;  
   
   FUNCTION DAYS_DIFF (IN_FROM_DATE DATE, IN_TO_DATE DATE)  
    RETURN NUMBER;  
 END MAHMOUD_BUSINESS_DAYS;  

Package Body

 CREATE OR REPLACE PACKAGE BODY MAHMOUD_BUSINESS_DAYS  
 AS  
   FUNCTION ADD_BUSINESSDAYS (IN_DATE DATE, IN_COUNT_DAYS NUMBER)  
    RETURN DATE  
   IS  
    LD$RETURN_DATE  DATE;  
   BEGIN  
    SELECT MAX (SUB.HOLIDAY_DATE + IN_COUNT_DAYS - SUB.DAYS_COUNT)  
     INTO LD$RETURN_DATE  
     FROM (SELECT HOLIDAY_DATE,  
            (HOLIDAY_DATE - IN_DATE)  
            - COUNT (1) OVER (ORDER BY HOLIDAY_DATE)  
             DAYS_COUNT  
         FROM HOLIDAYS_VIEW  
         WHERE HOLIDAY_DATE > IN_DATE  
        UNION  
        SELECT IN_DATE, 0 FROM DUAL) SUB  
     WHERE SUB.DAYS_COUNT <= IN_COUNT_DAYS;  
   
    RETURN LD$RETURN_DATE;  
   END;  
   
   FUNCTION SUBTRACT_BUSINESSDAYS (IN_DATE DATE, IN_COUNT_DAYS NUMBER)  
    RETURN DATE  
   IS  
    LD$RETURN_DATE  DATE;  
   BEGIN  
    SELECT MIN (HOLIDAY_DATE - IN_COUNT_DAYS + DAYS_COUNT)  
     INTO LD$RETURN_DATE  
     FROM (SELECT HOLIDAY_DATE,  
            (IN_DATE - HOLIDAY_DATE)  
            - COUNT (1) OVER (ORDER BY HOLIDAY_DATE DESC)  
             DAYS_COUNT  
         FROM HOLIDAYS_VIEW  
         WHERE HOLIDAY_DATE < IN_DATE  
        UNION  
        SELECT IN_DATE, 0 FROM DUAL)  
     WHERE DAYS_COUNT <= IN_COUNT_DAYS;  
   
    RETURN LD$RETURN_DATE;  
   END;  
   
   FUNCTION DAYS_DIFF (IN_FROM_DATE DATE, IN_TO_DATE DATE)  
    RETURN NUMBER  
   IS  
    LN$HOLIDAYS_COUNT  NUMBER;  
   BEGIN  
    SELECT COUNT (1)  
     INTO LN$HOLIDAYS_COUNT  
     FROM HOLIDAYS_VIEW  
     WHERE HOLIDAY_DATE BETWEEN IN_FROM_DATE AND IN_TO_DATE;  
   
    RETURN IN_TO_DATE - IN_FROM_DATE - LN$HOLIDAYS_COUNT + 1;  
   END;  
 END MAHMOUD_BUSINESS_DAYS;  

Test The Package
Let's Now test the package

 SELECT MAHMOUD_BUSINESS_DAYS.  
     ADD_BUSINESSDAYS (TO_DATE ('20-6-2012', 'DD-MM-YYYY'), 7)  
      after_7_work_days  
  FROM DUAL;  
 --7/1/2012  
   
 SELECT MAHMOUD_BUSINESS_DAYS.  
     SUBTRACT_BUSINESSDAYS (TO_DATE ('20-6-2012', 'DD-MM-YYYY'), 7)  
      before_7_work_days  
  FROM DUAL;  
 --6/11/2012  
   
 SELECT MAHMOUD_BUSINESS_DAYS.  
     DAYS_DIFF (TO_DATE ('20-6-2012', 'DD-MM-YYYY'),  
            TO_DATE ('20-7-2012', 'DD-MM-YYYY'))  
      days_diff  
  FROM DUAL;  
  --22  

Thanks