13 September, 2012

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

2 comments:

  1. I'm impressed, I have to admit. Rarely do I come across a blog that'ѕ
    both equally еԁucative and engaging,
    аnԁ let me tell you, you have hit the
    nail οn the head. The pгоblem is something
    which tοo few folks are ѕpeakіng іntelligently about.
    I am verу hapрy I ѕtumblеd acroѕs thіѕ during my seаrch for something
    cоncerning this.

    Тaκe a looκ at my ρage ... best payday loans
    Also see my webpage :: best payday loans

    ReplyDelete
  2. Great use of analytics to some a real world problem. There seems to be a slight logic error at least for adding small number of days during the week that will not cross a "weekend"

    create or replace view holidays_view as
    SELECT TO_DATE ('03-01-2020', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7) holiday_date
    FROM DUAL
    CONNECT BY LEVEL <= 65
    UNION
    SELECT TO_DATE ('04-01-2020', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)
    FROM DUAL
    CONNECT BY LEVEL <= 65
    /

    1 select holiday_date, to_char(holiday_date, 'Day') day
    2 from holidays_view
    3* where rownum <= 10
    SQL> /

    HOLIDAY_D DAY
    --------- ------------------------------------
    03-JAN-20 Friday
    04-JAN-20 Saturday
    10-JAN-20 Friday
    11-JAN-20 Saturday
    17-JAN-20 Friday
    18-JAN-20 Saturday
    24-JAN-20 Friday
    25-JAN-20 Saturday
    31-JAN-20 Friday
    01-FEB-20 Saturday

    10 rows selected.

    SELECT MAHMOUD_BUSINESS_DAYS.ADD_BUSINESSDAYS (TO_DATE ('01/01/2020', 'MM/DD/YYYY'), 1) after_7_work_days
    FROM DUAL
    /

    AFTER_7_W
    ---------
    04-JAN-20

    1 row selected.

    Should 02-JAN-20

    I modified to use just <

    SQL> SELECT MAHMOUD_BUSINESS_DAYS.ADD_BUSINESSDAYS (TO_DATE ('01/01/2020', 'MM/DD/YYYY'), 1) after_7_work_days from dual;

    AFTER_7_W
    ---------
    02-JAN-20

    1 row selected.

    SQL> SELECT MAHMOUD_BUSINESS_DAYS.ADD_BUSINESSDAYS (TO_DATE ('01/01/2020', 'MM/DD/YYYY'), 2) after_7_work_days from dual;

    AFTER_7_W
    ---------
    05-JAN-20

    1 row selected.

    I think WHERE SUB.DAYS_COUNT <= IN_COUNT_DAYS; should be WHERE SUB.DAYS_COUNT < IN_COUNT_DAYS;

    SQL> SELECT MAHMOUD_BUSINESS_DAYS.ADD_BUSINESSDAYS (TO_DATE ('01/01/2020', 'MM/DD/YYYY'), 1) after_7_work_days from dual;

    AFTER_7_W
    ---------
    02-JAN-20

    1 row selected.

    SQL> SELECT MAHMOUD_BUSINESS_DAYS.ADD_BUSINESSDAYS (TO_DATE ('01/01/2020', 'MM/DD/YYYY'), 2) after_7_work_days from dual;

    AFTER_7_W
    ---------
    05-JAN-20

    1 row selected.

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...