Calculate Hours Based on Business Hours in Oracle SQL

In Oracle SQL, calculating hours between two time periods can be straightforward. However, if you need to factor in business hours, the calculation becomes more complex.

Business Hours Specification

The provided business hours are Monday through Saturday from 8:00 AM to 6:00 PM. To incorporate these hours into the calculation, you can use a combination of date manipulation and conditional logic.

SQL Query

The following SQL query provides a solution for calculating hours based on the specified business hours:

SELECT task,
       start_time,
       end_time,
       ROUND(
         (
           -- Calculate the full weeks difference from the start of ISO weeks.
           ( TRUNC( end_time, 'IW' ) - TRUNC( start_time, 'IW' ) ) * (10/24) * (6/7)
           -- Add the full days for the final week.
             LEAST( TRUNC( end_time ) - TRUNC( end_time, 'IW' ), 6 ) * (10/24)
           -- Subtract the full days from the days of the week before the start date.
           - LEAST( TRUNC( start_time ) - TRUNC( start_time, 'IW' ), 6 ) * (10/24)
           -- Add the hours of the final day
             LEAST( GREATEST( end_time - TRUNC( end_time ) - 8/24, 0 ), 10/24 )
           -- Subtract the hours of the day before the range starts.
           - LEAST( GREATEST( start_time - TRUNC( start_time ) - 8/24, 0 ), 10/24 )
         )
         -- Multiply to give minutes rather than fractions of full days.
         * 24,
         15 -- Number of decimal places
       ) AS work_day_hours_diff
FROM   your_table;

Explanation

• Weeks: The query first calculates the full weeks within the range using TRUNC( end_time, 'IW' ) and TRUNC( start_time, 'IW' ). These calculations give the start and end dates of the ISO weeks containing the start and end of the task.
• Days: The LEAST() and GREATEST() functions are used to account for partial days at the beginning and end of the range, where business hours may not apply.
• Hours: The difference in hours for the final day and the day before the range starts are calculated similarly.
• Multiplication: The final calculation multiplies the result by 24 to convert minutes to hours.

Sample Data and Output

With the provided sample data:

TASK | START_TIME | END_TIME
A | 16-JAN-17 10:00 | 23-JAN-17 11:35
B | 18-JAN-17 17:53 | 19-JAN-17 08:00
C | 13-JAN-17 13:00 | 17-JAN-17 14:52
D | 21-JAN-17 10:00 | 30-JAN-17 08:52

The query outputs the following results:

These results accurately reflect the number of working hours based on the specified business hours.