时间戳

首页 > 解决方案 > 计算未结工单的通过服务时间 (Oracle SQL)

sql - 计算未结工单的通过服务时间 (Oracle SQL)

问题描述

我正在尝试根据服务时间计算自记录服务请求以来经过的时间（服务时间）。

开始时间是记录工单的时间 (date_logged)，结束时间可以是打开工单的当前时间或关闭工单的 date_closed。

服务时间为：

所以它应该看起来像这样（减去“当前日期时间”列，这只是为了上下文）：

票号 date_logged 服务时间 [hh:mm] 当前日期时间 日期关闭
1234567 06.01.21 11:30:52 62:33 14.01.2021 12:03
8912345 13.01.21 09:14:16 12:19 14.01.2021 12:03
6789012 14.01.21 10:48:01 00:28 14.01.2021 12:03 14.01.21 11:40
... ... ... ... ...

我还不能说是否必须包括公共假期，所以我们现在可以忽略这些。

我很感谢任何帮助！

标签： sqloracle

解决方案

您可以计算时间量（改编自我在此处和此处的回答）：

SELECT ticket_nr,
       date_logged,
       current_datetime,
       date_closed,
       TO_CHAR( FLOOR( service_time_seconds / 60 / 60 ), 'FM9990' )
       || ':'
       || TO_CHAR( MOD( FLOOR( service_time_seconds / 60 ), 60 ), 'FM00' )
       || ':'
       || TO_CHAR( MOD( service_time_seconds, 60 ), 'FM00' )
         AS "SERVICE_TIME HH:MM:SS"
FROM   (
SELECT ticket_nr,
       date_logged,
       SYSDATE AS current_datetime,
       date_closed,
       ROUND(
         (
           -- Calculate the full weeks difference from the start of ISO weeks.
           ( 
             TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' )
             - TRUNC( date_logged, 'IW' )
           ) * (9.5*4+6)/(7*24)
           -- Add the hours for the full days for the final week.
           + DECODE(
               TRUNC( COALESCE( date_closed, SYSDATE ) )
               - TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' ),
               0,  0.0,
               1,  9.5,
               2, 19.0,
               3, 28.5,
               4, 38.0,
                  44.0
             ) / 24
           -- Subtract the hours for the full days from the days of the week
           -- before the date logged.
           - DECODE(
               TRUNC( date_logged )
               - TRUNC( date_logged, 'IW' ),
               0,  0.0,
               1,  9.5,
               2, 19.0,
               3, 28.5,
               4, 38.0,
                  44.0
             ) / 24
           -- Add the hours of the final day
           + LEAST(
               GREATEST(
                 COALESCE( date_closed, SYSDATE )
                 - ( TRUNC( COALESCE( date_closed, SYSDATE ) )
                     + INTERVAL '07:00' HOUR TO MINUTE
                   ),
                 0
               ),
               DECODE(
                 TRUNC( COALESCE( date_closed, SYSDATE ) )
                 - TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' ),
                 0, 9.5,
                 1, 9.5,
                 2, 9.5,
                 3, 9.5,
                 4, 6.0,
                    0.0
               ) / 24
             )
           -- Subtract the hours of the day before the range starts.
           - LEAST(
               GREATEST(
                 date_logged
                 - ( TRUNC( date_logged ) + INTERVAL '07:00' HOUR TO MINUTE ),
                 0
               ),
               DECODE(
                 TRUNC( date_logged )
                 - TRUNC( date_logged, 'IW' ),
                 0, 9.5,
                 1, 9.5,
                 2, 9.5,
                 3, 9.5,
                 4, 6.0,
                    0.0
               ) / 24
             )
         )
         -- Multiply to give seconds rather than fractions of full days.
         * 24 * 60 * 60
       ) AS service_time_seconds
FROM   table_name
);

其中，对于样本数据：

CREATE TABLE table_name ( Ticket_Nr, date_logged, date_closed ) AS
SELECT 1234567, DATE '2021-01-06' + INTERVAL '11:30:52' HOUR TO SECOND, NULL FROM DUAL UNION ALL
SELECT 8912345, DATE '2021-01-13' + INTERVAL '09:14:16' HOUR TO SECOND, NULL FROM DUAL UNION ALL
SELECT 6789012, DATE '2021-01-14' + INTERVAL '10:48:28' HOUR TO SECOND, DATE '2021-01-21' + INTERVAL '11:40:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT       1, DATE '2021-01-07' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-14' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT       2, DATE '2021-01-07' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-08' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT       3, DATE '2021-01-08' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-09' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT       4, DATE '2021-01-09' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-10' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL

输出（在哪里NLS_DATE_FORMAT）YYYY-MM-DD HH24:MI:SS (DY)：

TICKET_NR | DATE_LOGGED | 当前日期时间 | DATE_CLOSED | SERVICE_TIME HH:MM:SS
--------: | :------------------------ | :------------------------ | :------------------------ | :--------------------
  1234567 | 2021-01-06 11:30:52 (星期三) | 2021-01-14 12:36:54 (星期四) | 空                      | 54:36:02             
  8912345 | 2021-01-13 09:14:16 (星期三) | 2021-01-14 12:36:54 (星期四) | 空                      | 12:52:38             
  6789012 | 2021-01-14 10:48:28 (星期四) | 2021-01-14 12:36:54 (星期四) | 2021-01-21 11:40:00 (星期四) | 44:51:32             
        1 | 2021-01-07 07:00:00 (星期四) | 2021-01-14 12:36:54 (星期四) | 2021-01-14 07:00:00 (星期四) | 44:00:00             
        2 | 2021-01-07 07:00:00 (星期四) | 2021-01-14 12:36:54 (星期四) | 2021-01-08 07:00:00 (周五) | 9:30:00              
        3 | 2021-01-08 07:00:00 (周五) | 2021-01-14 12:36:54 (星期四) | 2021-01-09 07:00:00 (周六) | 6:00:00              
        4 | 2021-01-09 07:00:00 (周六) | 2021-01-14 12:36:54 (星期四) | 2021-01-10 07:00:00 (星期日) | 0:00:00

db<>在这里摆弄

推荐阅读