AnsweredAssumed Answered

SQL for Sync Wait Time

Question asked by BenCroffut603954 on Dec 9, 2016
Latest reply on Dec 10, 2016 by BenCroffut603954
Has anyone out there created SQL to identify how long  jobs were in a sync? I've checked the EY table which has the last status for a task as long as it's in the Activities window but I'm looking to determine for a large number of jobs when they were in SYNC_WAIT status and then when they changed status to ACTIVE or other statuses.

I have some old sql but I'm not getting results with it.

WITH   SYNC_TIMES AS (     SELECT DISTINCT       OH_NAME     , FIRST_VALUE(NEW_TIME(RT_TIMESTAMP, 'GMT', 'PDT'))         OVER (PARTITION BY AH_IDNR ORDER BY RT_MSGNR ASC, RT_LNR ASC) AS WAIT_START_TIME     , FIRST_VALUE(NEW_TIME(RT_TIMESTAMP, 'GMT', 'PDT'))         OVER (PARTITION BY AH_IDNR ORDER BY RT_MSGNR DESC, RT_LNR ASC) AS WAIT_END_TIME     FROM uc4.OH     INNER JOIN uc4.OT ON ot_oh_idnr = oh_idnr     INNER JOIN uc4.AH ON ah_oh_idnr = oh_idnr     INNER JOIN uc4.RH ON rh_ah_idnr = ah_idnr     INNER JOIN uc4.RT ON rt_rh_idnr = rh_idnr     WHERE       oh_client = 1000       AND oh_otype = 'JOBS'       AND ot_type = 0       AND ot_content LIKE '%multi_unload_handler.ksh%'       AND TRUNC(NEW_TIME(ah_timestamp1, 'GMT', 'PDT')) > TRUNC(SYSDATE - 7)       AND ah_status = 1900       AND rh_type = 'ACT'       AND rt_msgnr IN (4406, 4408)   ) SELECT   OH_NAME , TO_CHAR((TRUNC(SYSDATE) +     (AVG(EXTRACT(HOUR FROM CAST(WAIT_END_TIME AS TIMESTAMP))/24       +  EXTRACT(MINUTE FROM CAST(WAIT_END_TIME AS TIMESTAMP))/1440       +  EXTRACT(SECOND FROM CAST(WAIT_END_TIME AS TIMESTAMP))/86400)     )   - (AVG(EXTRACT(HOUR FROM CAST(WAIT_START_TIME AS TIMESTAMP))/24       +  EXTRACT(MINUTE FROM CAST(WAIT_START_TIME AS TIMESTAMP))/1440       +  EXTRACT(SECOND FROM CAST(WAIT_START_TIME AS TIMESTAMP))/86400)     )   ), 'HH24:MI:SS') AS AVG_WAIT_TIME FROM SYNC_TIMES GROUP BY   oh_name ORDER BY   AVG_WAIT_TIME

Outcomes