#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    3
    Rep Power
    0

    Calculate business days


    I have below listed Oracle function to calculate business days (holidays).

    It works for date forward but i need dates to calculate backwards.

    For EX: date :11/14/13 should show as -2 as it is future date compared to today's date

    CREATE OR REPLACE FUNCTION calculate_business_days (p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER IS
    v_holidays NUMBER;
    day date;
    v_start_date DATE := TRUNC (p_start_date);
    v_end_date DATE := TRUNC (p_end_date);
    BEGIN
    IF v_end_date >= v_start_date
    THEN
    SELECT COUNT (*)
    INTO v_holidays
    FROM PCI_HOLIDAY_LIST
    WHERE day BETWEEN v_start_date AND v_end_date
    AND day NOT IN (
    SELECT HOLIDAY_DT FROM PCI_HOLIDAY_LIST hol wheRE MOD(TO_CHAR(HOLIDAY_DT, 'J'), 7) + 1 IN (6, 7)
    );

    RETURN GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)
    + ( ( NEXT_DAY (v_end_date, 'MON')
    - NEXT_DAY (v_start_date, 'MON')
    )
    / 7
    )
    * 5
    - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)
    - v_holidays;
    ELSE
    RETURN NULL;
    END IF;
    END calculate_business_days;
    /
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    840
    Rep Power
    387

    Cool


    Originally Posted by reddy_sai
    . . .
    It works for date forward but i need dates to calculate backwards.

    For EX: date :11/14/13 should show as -2 as it is future date compared to today's date
    . . . E t c . . .
    backwards???
    Just a matter of switching the dates in the function, but try this:
    Code:
    CREATE OR REPLACE FUNCTION calculate_business_days 
    ( p_start_date IN DATE
    , p_end_date IN DATE)
      RETURN NUMBER
    IS
      v_holidays     NUMBER;
      day            DATE;
      v_start_date   DATE := TRUNC ( p_start_date);
      v_end_date     DATE := TRUNC ( p_end_date);
      v_sign         NUMBER := 1;
    BEGIN
      IF v_end_date < v_start_date
      THEN
        v_start_date   := TRUNC ( p_end_date);
        v_end_date     := TRUNC ( p_start_date);
        v_sign         := -1;
      END IF;
    
      IF v_end_date > TRUNC ( SYSDATE)
      THEN
        v_sign   := -1;
      END IF;
    
      SELECT COUNT ( *)
        INTO v_holidays
        FROM pci_holiday_list
       WHERE day BETWEEN v_start_date AND v_end_date
         AND TO_CHAR ( day) NOT IN (1, 7);
    
      RETURN   v_sign
             * (  GREATEST ( NEXT_DAY ( v_start_date, 'MON') - v_start_date - 2, 0)
                +   (  (NEXT_DAY ( v_end_date, 'MON') - NEXT_DAY ( v_start_date, 'MON'))
                     / 7)
                  * 5
                - GREATEST ( NEXT_DAY ( v_end_date, 'MON') - v_end_date - 3, 0)
                - v_holidays);
    END calculate_business_days;
    /
    And please try to format your code!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    backwards???
    Just a matter of switching the dates in the function, but try this:
    Code:
    CREATE OR REPLACE FUNCTION calculate_business_days 
    ( p_start_date IN DATE
    , p_end_date IN DATE)
      RETURN NUMBER
    IS
      v_holidays     NUMBER;
      day            DATE;
      v_start_date   DATE := TRUNC ( p_start_date);
      v_end_date     DATE := TRUNC ( p_end_date);
      v_sign         NUMBER := 1;
    BEGIN
      IF v_end_date < v_start_date
      THEN
        v_start_date   := TRUNC ( p_end_date);
        v_end_date     := TRUNC ( p_start_date);
        v_sign         := -1;
      END IF;
    
      IF v_end_date > TRUNC ( SYSDATE)
      THEN
        v_sign   := -1;
      END IF;
    
      SELECT COUNT ( *)
        INTO v_holidays
        FROM pci_holiday_list
       WHERE day BETWEEN v_start_date AND v_end_date
         AND TO_CHAR ( day) NOT IN (1, 7);
    
      RETURN   v_sign
             * (  GREATEST ( NEXT_DAY ( v_start_date, 'MON') - v_start_date - 2, 0)
                +   (  (NEXT_DAY ( v_end_date, 'MON') - NEXT_DAY ( v_start_date, 'MON'))
                     / 7)
                  * 5
                - GREATEST ( NEXT_DAY ( v_end_date, 'MON') - v_end_date - 3, 0)
                - v_holidays);
    END calculate_business_days;
    /
    And please try to format your code!

    I need to calculate forward and backward too..

    I dont think holidays are working..

    thanks for your help
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    840
    Rep Power
    387
    Originally Posted by reddy_sai
    I need to calculate forward and backward too..

    I dont think holidays are working..

    thanks for your help
    Ooops typo, change this:
    Code:
    --.... . . . . Here . . . . vvvv
        AND TO_CHAR ( day, 'D' ) NOT IN (1, 7);
    -- etc ...
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    3
    Rep Power
    0
    i updated as below and it is working now

    TO_CHAR ( holiday_dt,'DD-MON-YYYY') BETWEEN v_start_date AND v_end_date;

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo