stunden basierend auf den Geschäftszeiten in oracle sql

In Oracle SQL können Stunden zwischen zwei Zeiträumen berechnet werden. Wenn Sie jedoch die Geschäftszeiten berücksichtigen müssen, wird die Berechnung komplexer.

Geschäftszeitenspezifikation

Die bereitgestellten Geschäftszeiten sind Montag bis Samstag von 8:00 Uhr Bin bis 18.00 Uhr. Um diese Stunden in die Berechnung einzubeziehen, können Sie eine Kombination aus Manipulation und bedingter Logik verwenden.

sql query

Die folgende SQL -Abfrage liefert eine Lösung für die Berechnung der Stunden Basierend auf den angegebenen Geschäftszeiten:

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;

Erklärung

• weeks: Die Abfrage berechnet zuerst die vollen Wochen innerhalb des Bereichs mit TRUNC (end_time, 'iw ') und Trunc (start_time,' iw '). Diese Berechnungen geben die Start- und Enddaten der ISO -Wochen mit Start und Ende der Aufgabe. Tage am Anfang und am Ende des Bereichs, in dem die Geschäftszeiten möglicherweise nicht gelten.
• Stunden: Der Unterschied in den Stunden für den letzten Tag und den Tag vor dem Start des Sortiments werden ähnlich berechnet.
• multiplication: Die endgültige Berechnung multipliziert das Ergebnis um 24, um Minuten in Stunden umzuwandeln.
• Beispieldaten und Ausgabe

mit den bereitgestellten Beispieldaten: Aufgabe | 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

Die Abfrage gibt die folgenden Ergebnisse aus:

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

Diese Ergebnisse spiegeln genau die Anzahl der Arbeitszeiten auf der Grundlage der angegebenen Geschäftszeiten wider.