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

    Join Date
    Apr 2008
    Posts
    61
    Rep Power
    10

    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
    867
    Rep Power
    391

    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
    61
    Rep Power
    10

    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