TIME DIFFERENCE EXCLUDING WEEKENDS/BANK HOLIDAYS AND HRS BETWEEN 8 TO 6PM

I need to get the time difference between 2 dates excluding weekends/bank holidays and only hours between 9am to 5pm (office hours)
my table
REF        STARTDATE                    ENDDATE
001        01/06/2007                      28/09/2007

Any help much appreciated.
Phil
philsivyerAsked:
Who is Participating?
 
MikeOM_DBAConnect With a Mentor Commented:

You need to code your own function to do it.
Start  by customizing the following function gleaned from Oracle Resource Stop:

rem Script Description: This procedure determines the business days between two dates.
rem                     There are many times in reports or in calculations for forms
rem                     where the number of business days between two dates needs to be
rem                     determined. Here is a method for calculating this (excluding holidays).
rem
rem                     The following SQL script shows an algorythm that uses the standard
rem                     Oracle date functions to calculate the number of business days between
rem                     to dates. This method cannot exclude holidays (obviously), however
rem                     there are many times that just the standard business days is useful.
rem                     There are other algorythms that could be used, however this one works
rem                     well. The algorythm is described below in the comments of this script.
rem
rem
rem                     ***************************************************************
rem                     An example of business days calculation in pseudo-code
rem                                                              
rem                     The algorythm is:                                            
rem                                                              
rem                     1) Take the absolute difference between the dates          
rem                        TRUNC(p_to_date) - to_date(p_from_date)                
rem                     2) Subtract the weekends (number of weeks in the range      
rem                        TRUNC(p_to_date,'D') = 1st day of week that  
rem                                                        end of period is in    
rem                        TRUNC(p_from_date,'D') = Last day of week that  
rem                                                        start of period is in  
rem                        So subtracting these two gives the number of days      
rem                        between the two dates but including all of the days in
rem                        the weeks that the dates start and end in.  When this  
rem                        number is divided by 7 it gives the number of weeks.  
rem                        Multiplying by 2 gives the number of weekend days.       
rem                     3) Add one day to make the range inclusive (The '1 + ' )    
rem                     4) Subtract 1 day if the ending date is on a saturday      
rem                        DECODE(to_char(p_to_date,'D'),7,-1,0)        
rem                        --> If the day of the week is saturday (7), returns -1
rem                     4) Subtract 1 day if the start date is on a sunday          
rem                        DECODE(to_char(p_from_date,'D'),1,-1)          
rem                        --> If the day of the week is sunday (1), returns 1    
rem
rem                     A note concerning Holiday considerations:
rem                     It is possible to account for holidays as well. By performing the
rem                     following changes:
rem                  
rem                     1) Calculate the number of holidays that fall between the from_date
rem                        and to_date values.
rem
rem                        SELECT COUNT(*) INTO nHolidays
rem                        FROM Your_Holiday_Table
rem                        WHERE holiday_date BETWEEN p_from_date AND p_to_date;
rem
rem                     2) Then substract nHolidays from your business_days variable before
rem                        returning.
rem                     ***************************************************************
rem
rem
rem Prepared By:        Oracle Resource Stop
rem
rem Usage Information:  See Below
rem
CREATE OR REPLACE FUNCTION business_days(p_from_date IN DATE, p_to_date IN DATE)
   RETURN NUMBER IS
busdays NUMBER;
BEGIN
  -- Get the absolute date range
  busdays := TRUNC(p_to_date) - TRUNC(p_from_date)  
        -- Now subtract the weekends
            --  this statement rounds the range to whole weeks (using
            --  TRUNC and determines the number of days in the range.
            --  then it divides by 7 to get the number of weeks, and
            --  multiplies by 2 to get the number of weekend days.
       - ((TRUNC(p_to_date,'D')-TRUNC(to_date(p_from_date),'D'))/7)*2
            -- Add one to make the range inclusive
       + 1;

  rem Adjust for ending date on a saturday
  IF TO_CHAR(p_to_date,'D') = '7' THEN
    busdays := busdays - 1;
  END IF;

  rem Adjust for starting date on a sunday
  IF TO_CHAR(p_from_date,'D') = '1' THEN
    busdays := busdays - 1;
  END IF;
  RETURN(busdays);
END;
/
show errors;

/*********************************************************************************************
Rem  The following is a sample of possible usage for the business_days procedure
Rem  This sample will calculate business days between a given date.
********************************************************************************************
SQL*Plus> select business_days('01-AUG-96','15-AUG-96') from dual;

BUSINESS_DAYS('01-AUG-96','15-AUG-96')                                          
--------------------------------------                                          
                                    11                                          

1 row selected.
*/


0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
There is no other way out apart from building our own logic for this question. We have similar functionality but in one table, all business happening at one location say london in a company refer to a table called business-days.

This table will not have weekend dates and also this table will not have any record for a holiday.

Thanks
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.