Calcule las horas basadas en las horas comerciales en Oracle SQL

en Oracle SQL, calcular las horas entre dos períodos de tiempo puede ser sencillo. Sin embargo, si necesita tener en cuenta el horario comercial, el cálculo se vuelve más complejo.

Especificación de horario comercial

Los horarios comerciales proporcionados son de lunes a sábado de 8:00 AM a las 6:00 pm. Para incorporar estas horas en el cálculo, puede usar una combinación de manipulación de fecha y lógica condicional.

sql consuly

La siguiente consulta SQL proporciona una solución para calcular horas calculando horas calculadoras Basado en el horario comercial especificado:

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;

explicación

• semanas: La consulta calcula primero las semanas completas dentro del rango usando trunc (end_time, 'iww,' iw ') y trunc (start_time,' iw '). Estos cálculos dan las fechas de inicio y finalización de las semanas ISO que contienen el inicio y el final de la tarea.
• días: las funciones menos () y grandes () se usan para tener en cuenta parcial días al principio y al final del rango, donde las horas hábiles no pueden aplicarse.
• horas: la diferencia en las horas para el día final y el día antes de que comience el rango se calcula de manera similar.
• Multiplicación: El cálculo final multiplica el resultado por 24 para convertir minutos a horas.

muestra datos y salida

con los datos de muestra proporcionados:

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

La consulta genera los siguientes resultados: