Calculate the amount of time spent

Question

I have a sql query that calculates the elapsed time for parallel jobs. Here is the request:

SELECT
             user_concurrent_program_name,
             DECODE (phase_code, 'C', 'Completed', phase_code) phase_code,
             DECODE (status_code,
                     'D', 'Cancelled',
                     'E', 'Error',
                     'G', 'Warning',
                     'H', 'On Hold',
                     'T', 'Terminating',
                     'M', 'No Manager',
                     'X', 'Terminated',
                     'C', 'Normal',
                     status_code)
                AS status_code,
          TO_CHAR (
              to_date('2001,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
               + (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE),
               'HH24:MI:SS')
            AS GECEN_SURE
        FROM apps.fnd_conc_req_summary_v
       WHERE phase_code = 'C' AND status_code = 'C';

Here are a few lines when I run it:

Initial Mass Copy                       Completed   Normal  22:12:35
Gather Schema Statistics                Completed   Normal  06:13:35
Request Set FAH-KPK Fislerini Olustur   Completed   Normal  04:36:36
Request Set FAH-KPK Fislerini Olustur   Completed   Normal  04:15:18
Request Set FAH-KPK Fislerini Olustur   Completed   Normal  04:13:45
Request Set FAH-KPK Fislerini Olustur   Completed   Normal  04:10:51

I want to get the amount of time spent. So my query result will look like this:

    Initial Mass Copy                           Completed   Normal  22:12:35
    Gather Schema Statistics                Completed   Normal  06:13:35
    Request Set FAH-KPK Fislerini Olustur   Completed   Normal  17:02:36

Answer 1

You can do it like in this fiddle

CREATE TABLE test
(
   A   CHAR (1),
   b   VARCHAR2 (10)
);

INSERT INTO TEST
     VALUES ('A', '04:00:01');

INSERT INTO TEST
     VALUES ('A', '04:00:01');

INSERT INTO TEST
     VALUES ('A', '13:02:01');

INSERT INTO TEST
     VALUES ('A', '11:00:01');

INSERT INTO TEST
     VALUES ('A', '02:59:59');

COMMIT;

SELECT    TO_CHAR (TRUNC (total / 3600))
       || ':'
       || TO_CHAR (TRUNC (ABS (MOD (total, 3600)) / 60), 'fm00')
       || ':'
       || TO_CHAR (MOD (total, 60), 'fm00')
  FROM (SELECT SUM (
                    TO_NUMBER (SUBSTR (b, 1, 2)) * 60 * 60
                  + TO_NUMBER (SUBSTR (b, 4, 2)) * 60
                  + TO_NUMBER (SUBSTR (b, 7, 2)))
                  TOTAL
          FROM TEST);
35:02:03

PS: This may be included in your aggregation unit.

So it should be something like this

WITH TEST
     AS (SELECT user_concurrent_program_name,
                DECODE (phase_code, 'C', 'Completed', phase_code) phase_code,
                DECODE (status_code,
                        'D', 'Cancelled',
                        'E', 'Error',
                        'G', 'Warning',
                        'H', 'On Hold',
                        'T', 'Terminating',
                        'M', 'No Manager',
                        'X', 'Terminated',
                        'C', 'Normal',
                        status_code)
                   AS status_code,
                TO_CHAR (
                     TO_DATE ('2001,091,00:00:00', 'YYYY,DDD,HH24:MI:SS')
                   + (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE),
                   'HH24:MI:SS')
                   AS GECEN_SURE
           FROM apps.fnd_conc_req_summary_v
          WHERE phase_code = 'C' AND status_code = 'C')
SELECT user_concurrent_program_name,
       phase_code,
       status_code,
          TO_CHAR (TRUNC (GECEN_SURE/ 3600))
       || ':'
       || TO_CHAR (TRUNC (ABS (MOD (GECEN_SURE, 3600)) / 60), 'fm00')
       || ':'
       || TO_CHAR (MOD (GECEN_SURE, 60), 'fm00') TOTAL
  FROM (  SELECT user_concurrent_program_name,
                 phase_code,
                 status_code,
                 SUM (
                      TO_NUMBER (SUBSTR (GECEN_SURE, 1, 2)) * 60 * 60
                    + TO_NUMBER (SUBSTR (GECEN_SURE, 4, 2)) * 60
                    + TO_NUMBER (SUBSTR (GECEN_SURE, 7, 2)))
                    GECEN_SURE
            FROM TEST
        GROUP BY user_concurrent_program_name, phase_code, status_code);

Answer 2

, . , , , . - , .

, .

:

with d as (
    -- 1 day duration
    select 'X' as typ, sysdate - 4 as start_dte, sysdate - 3 as end_dte from dual
    union all
    -- 1.5 days duration
    select 'X' as typ, sysdate - 3 as start_dte, sysdate - 1.5 as end_dte from dual
    union all
    -- 0.5 days duration
    select 'Y' as typ, sysdate - 1.5 as start_dte, sysdate - 1 as end_dte from dual
    union all
    -- 1 day duration
    select 'Z' as typ, sysdate - 1 as start_dte, sysdate as end_dte from dual
)
-- subtract timestamp gives intervals
select typ, sum_interval(cast(end_dte as timestamp) - cast(start_dte as timestamp)) as duration
from d
group by typ;

:

TYP DURATION
X   +02 12:00:00.000000
Y   +00 12:00:00.000000
Z   +01 00:00:00.000000