How to Calculate Elapsed Hours Considering Business Hours in Oracle SQL?

Calculating Hours Based on Business Hours in Oracle SQL

To measure elapsed hours between start and end times while considering business hour constraints, we need to incorporate business day limitations into our calculations. Here are approaches to achieve this in Oracle SQL:

Direct Calculation with Business Hour Adjustments:

This method directly computes the hour difference with adjustments based on defined business hours:

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

Hierarchical Query with Business Day Generation:

Alternatively, we can generate one row for each business day and calculate hours per day, then sum them:

SELECT task,
       COALESCE( SUM( end_time - start_time ), 0 ) * 24 AS total_hours
FROM   (
  SELECT task,
         GREATEST( t.start_time, d.column_value + INTERVAL '8' HOUR ) AS start_time,
         LEAST( t.end_time, d.column_value + INTERVAL '18' HOUR ) AS end_time
  FROM   your_table t
         LEFT OUTER JOIN
         TABLE(
           CAST(
             MULTISET(
               SELECT TRUNC( t.start_time + LEVEL - 1 )
               FROM   DUAL
               WHERE  TRUNC( t.start_time + LEVEL - 1 ) - TRUNC( t.start_time + LEVEL - 1, 'iw' ) < 6
               CONNECT BY TRUNC( t.start_time + LEVEL - 1 ) < t.end_time
             ) AS SYS.ODCIDATELIST
           )
         ) d
         ON (   t.end_time   > d.column_value + INTERVAL  '8' HOUR
            AND t.start_time < d.column_value + INTERVAL '18' HOUR )
)
GROUP BY task;

Both methods consider Monday through Saturday, 08:00 to 18:00 as business hours. Make sure to adjust the business hours definition in the queries if your business hours differ.

The above is the detailed content of How to Calculate Elapsed Hours Considering Business Hours in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!