?
Solved

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

Posted on 2007-10-01
4
Medium Priority
?
6,532 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:philsivyer
3 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 1000 total points
ID: 19991404

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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 1000 total points
ID: 19994559
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20238154
Forced accept.

Computer101
EE Admin
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month17 days, 10 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question