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

    Join Date
    Apr 2008
    Posts
    56
    Rep Power
    7

    Calculate working days with oracle function


    Hi All

    Could anyone help pls, how do i can make oracle function for the following vb6 function.


    This function is used for calculating working days between two date.

    ****************************************

    Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

    ' Note that this function does not account for holidays.

    Dim WholeWeeks As Variant
    Dim DateCnt As Variant
    Dim EndDays As Integer

    On Error GoTo Err_Work_Days

    BegDate = DateValue(BegDate)
    EndDate = DateValue(EndDate)
    WholeWeeks = DateDiff("w", BegDate, EndDate)
    DateCnt = DateAdd("ww", WholeWeeks, BegDate)
    EndDays = 0
    Do While DateCnt <= EndDate
    ' MsgBox Format(DateCnt, "ddd")
    If Format(DateCnt, "ddd") <> "Sun" And _
    Format(DateCnt, "ddd") <> "Sat" Then
    EndDays = EndDays + 1
    End If
    DateCnt = DateAdd("d", 1, DateCnt)
    Loop
    Work_Days = WholeWeeks * 5 + EndDays

    Exit Function

    Err_Work_Days:

    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates. '

    If Err.Number = 94 Then
    Work_Days = 0
    Exit Function
    Else
    ' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

    End Function


    Thanks for any kind help.
    Regards
    Mchoud
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Talking


    +
    Check out this Oracle tip.

    Or, You may need to create a holiday_days table to hold the days that you take as holiday, then create a function that returns the working days as follows:

    Code:
    --(Courtesy of Rod West):
    CREATE OR REPLACE FUNCTION working_days (p_start_date DATE, p_end_date DATE)
       RETURN NUMBER
    IS
       v_holidays     NUMBER;
       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 (holiday)
            INTO v_holidays
            FROM holidays_days
           WHERE holiday BETWEEN v_start_date AND v_end_date;
    
          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 working_days;
    /
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    56
    Rep Power
    7

    Thanks for the reply


    This is very good and useful function.
    That is beyond my expectation.

    Thanks very much
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0
    The following given code is not full correct.
    Code:
    --(Courtesy of Rod West): 
    CREATE OR REPLACE FUNCTION working_days (p_start_date DATE, p_end_date DATE)    
    RETURN NUMBER IS    
    v_holidays     NUMBER;    
    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 (holiday)         
    INTO v_holidays         
    FROM holidays_days        
    WHERE holiday BETWEEN v_start_date AND v_end_date;        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 working_days; /
    If a holiday is a weekend, it will subtract that date 2x. I have fixed this bug in the following code...

    Code:
    -- (Courtesy of Marlon Christiaan): 
    create or replace 
    FUNCTION calculate_business_days (p_start_date IN DATE, p_end_date IN DATE)    
    RETURN NUMBER IS    
    v_holidays     NUMBER;
    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 holidays        
    WHERE day BETWEEN v_start_date AND v_end_date        
    AND day NOT IN (
    SELECT hol.day 
    FROM holidays hol 
    WHERE MOD(TO_CHAR(hol.day, '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;

IMN logo majestic logo threadwatch logo seochat tools logo