Teradata Maximum process end time capture for multiple processes

Question

I am trying to fetch data for one asset for reporting purposes which is dependent on multiple processes and start time should be the earliest job that starts among them and end time should be latest completion time for one of those assets. I prepared one query which is running fine if asset has only one process but need help for multiple processes. Below are the details:

Table Name: ABC.ASSET_CONFIG
              
 ASSET_ID,APPLICATION,ASSET_NAME,MODULE_NAME,ASSET_DESCRIPTION,ASSET_TYPE
 9999,Marketing,Activation Data Mart,ADM,NA,Business Necessary

Table Name: ABC.ASSET_DEPENDENCY_CONFIG
         ASSET_ID,COMPLETING_PROCESS_ID,DEPENDENCY_LEVEL,DATA_DELAY,PRCS_CNTRL_STORE_TYPE                                          
         9999,206,1,-1,ABC.BATCH_CONTROL
         9999,208,1,-1,ABC.BATCH_CONTROL
         9999,508,1,-1,ABC.BATCH_CONTROL
         9999,1024,1,-1,ABC.BATCH_CONTROL

One asset may be related to multiple processes(COMPLETING_PROCESS_ID) which is completing at different times, all needs to be completed(Status as 'C' in BATCH_CONTROL table) to be able to get the full dataset of that asset. We have table(ABC.BATCH_CONTROL) where completion status of each processes are tracked. Status: 'C' denotes - Process Completed.

  Table Name: ABC.BATCH_CONTROL
       DATA_SOURCE_ID,BATCH_RUN_ID,LOAD_START_TS,LOAD_END_TS,BATCH_START_TS,BATCH_END_TS,BATCH_STATUS_CD
      206,784,'2023-05-28 00:00:00','2023-05-29 00:00:00','2023-05-29 13:22:00','2023-05-29 14:36:00','C'
      206,783,'2023-05-27 00:00:00','2023-05-28 00:00:00','2023-05-28 13:23:00','2023-05-28 14:38:00','C'
      206,782,'2023-05-26 00:00:00','2023-05-27 00:00:00','2023-05-27 13:22:00','2023-05-27 15:21:00','C'
      206,781,'2023-05-25 00:00:00','2023-05-26 00:00:00','2023-05-26 13:25:00','2023-05-26 15:04:00','C'

       1024,2224,'2023-05-28 00:00:00','2023-05-29 00:00:00','2023-05-29 17:50:00','2023-05-29 18:23:00','C'
       1024,2223,'2023-05-27 00:00:00','2023-05-28 00:00:00','2023-05-28 17:50:00','2023-05-28 18:21:00','C'
       1024,2222,'2023-05-26 00:00:00','2023-05-27 00:00:00','2023-05-27 17:46:00','2023-05-27 18:22:00','C'
       1024,2221,'2023-05-25 00:00:00','2023-05-26 00:00:00','2023-05-26 17:48:00','2023-05-26 18:18:00','C'

I tried below code which is working fine for a asset which has only one process, if an asset data readiness depends on multiple multiple processes it won't work:

Output i want is:

     9999,Marketing,Business Necessary,Activation Data Mart,ADM,1024,2023-05-28,2023-05-29 13:22:00,2023-05-29 17:50:00, 04:12:23,0 04:12:23.000000,0 00:04:12.0000,203.0000,Completed

Query:

    SELECT 
    QRY3.ASSET_ID,     
    QRY3.APPLICATION,
    QRY3.ASSET_TYP,
    QRY3.ASSET_NAME,
    QRY3.MODULE_NAME,
    QRY3.COMPLETING_PROCESS_ID,
    QRY3.DATA_DATE,
    QRY3.START_TIME,
    QRY3.END_TIME,
    SUBSTRING((TRIM((CAST(((CAST(QRY3.END_TIME AS TIMESTAMP(0)) - CAST(QRY3.START_TIME AS  TIMESTAMP(0))) DAY(4) TO SECOND)AS VARCHAR(50))))),2,9) AS DURATION,
    (TRIM((CAST(((CAST(QRY3.END_TIME AS TIMESTAMP(0)) - CAST(QRY3.START_TIME AS TIMESTAMP(0)))  DAY(4) TO SECOND)AS VARCHAR(50))))) AS DURATION_TIMESTAMP,
    --((CAST(QRY3.END_TIME AS TIMESTAMP(0)) - CAST(QRY3.START_TIME AS TIMESTAMP(0)))          Second(10,6)) AS DURATION_TIMESTAMP_SECOND,
    ((CAST(QRY3.END_TIME AS TIMESTAMP(0)) - QRY3.START_TIME) DAY(4) to SECOND(4)) AS t1,
        (EXTRACT(DAY from t1)*(24*60*60) +
            EXTRACT(HOUR from t1)*(60*60) + 
            EXTRACT(MINUTE from t1)*60 +
            EXTRACT(SECOND from t1)
            ) AS DURATION_SECONDS,
    CASE WHEN ETL1.BATCH_STATUS_CD = 'C' THEN 'Completed'
        WHEN ETL1.BATCH_STATUS_CD = 'R' THEN 'Running'
        WHEN ETL1.BATCH_STATUS_CD = 'N' THEN 'Not Started'
        WHEN ETL1.BATCH_STATUS_CD = 'F' THEN 'Failed'
    END AS STATUS
    FROM
    (
    SELECT 
    QRY2.ASSET_ID,
    QRY2.APPLICATION,
    QRY2.ASSET_TYP,
    QRY2.ASSET_NAME,
    QRY2.MODULE_NAME,
    QRY2.COMPLETING_PROCESS_ID,
    QRY2.DATA_DATE
    ,MIN(QRY2.START_TIME) OVER(PARTITION BY  QRY2.APPLICATION,QRY2.ASSET_TYP,QRY2.ASSET_NAME,QRY2.MODULE_NAME,QRY2.COMPLETING_PROCESS_ID,QRY2.DATA_DATE)AS START_TIME
    ,MAX(QRY2.END_TIME) OVER(PARTITION BY  QRY2.APPLICATION,QRY2.ASSET_TYP,QRY2.ASSET_NAME,QRY2.MODULE_NAME,QRY2.COMPLETING_PROCESS_ID,QRY2.DATA_DATE)AS END_TIME
    ,MAX(QRY2.BATCH_RUN_ID) OVER(PARTITION BY   QRY2.APPLICATION,QRY2.ASSET_TYP,QRY2.ASSET_NAME,QRY2.MODULE_NAME,QRY2.COMPLETING_PROCESS_ID,QRY2.DATA_DATE) AS BATCH_RUN_ID
    FROM
    (
    SELECT 
    QRY1.ASSET_ID,
    QRY1.APPLICATION,
    QRY1.ASSET_TYP,
    QRY1.MODULE_NAME,
    QRY1.ASSET_NAME, 
    QRY1.COMPLETING_PROCESS_ID,
    QRY1.DATA_DATE AS DATA_DATE,
    ETL.BATCH_STATUS_CD,
    CASE WHEN
        QRY1.COMPL_SESSION_NAME IS NOT NULL 
            THEN SESS.SESSION_INSTANCE_START_TS 
        ELSE 
            ETL.BATCH_START_TS
    END AS START_TIME,
    CASE WHEN
        QRY1.COMPL_SESSION_NAME IS NOT NULL 
            THEN SESS.SESSION_INSTANCE_END_TS 
        ELSE 
            ETL.BATCH_END_TS
    END AS END_TIME,
    ETL.BATCH_RUN_ID
    FROM
    (
    select MASTER.ASSET_ID,
    MASTER.APPLICATION,
    MASTER.ASSET_TYP,
    MASTER.MODULE_NAME,
    MASTER.ASSET_NAME, 
    DEPEND.COMPLETING_PROCESS_ID,
    CAL.CALENDAR_DAY_DT AS RUN_DATE,
    cast(CAL.CALENDAR_DAY_DT as format 'YYYY-MM-DD')+ cast(DEPEND.DATA_DELAY as interval DAY)     AS DATA_DATE,
    DEPEND.COMPL_SESSION_NAME
    from 
    ABC.ASSET_CONFIG MASTER
    INNER JOIN
    ABC.ASSET_DEPENDENCY_CONFIG DEPEND
    ON
    MASTER.ASSET_ID = DEPEND.ASSET_ID
    inner join
    ndw_base_views.fiscal_calendar cal
    on
    1=1
    and 
    CALENDAR_DAY_DT BETWEEN '2023-05-27' AND '2023-05-27' 
    where
    DEPEND.PRCS_CNTRL_STORE_TYP = 'NDW_PRCS_CNTRL_VIEWS.NDW_ETL_BATCH_CONTROL'
    GROUP BY 1,2,3,4,5,6,7,8,9
    )QRY1
    LEFT JOIN
    ABC.BATCH_CONTROL ETL
    ON
    ETL.DATA_SOURCE_ID= QRY1.COMPLETING_PROCESS_ID
    AND ETL.INCREMENTAL_LOAD_START_TS = QRY1.DATA_DATE
    --LEFT JOIN
    --ABC.NDW_ETL_WORKFLOW_CONTROL WF
    --ON WF.DATA_SOURCE_ID = ETL.DATA_SOURCE_ID
    --AND ETL.BATCH_RUN_ID = WF.BATCH_RUN_ID
    --LEFT JOIN
    --ABC.NDW_ETL_SESSION_CONTROL SESS
    --ON WF.WORKFLOW_RUN_ID = SESS.WORKFLOW_RUN_ID
    --AND SESS.SESSION_INSTANCE_NM = QRY1.COMPL_SESSION_NAME
    --WHERE QRY1.ASSET_ID = 10080
    ) QRY2
    ) QRY3
    INNER JOIN
    ABC.BATCH_CONTROL ETL1
    ON
    ETL1.DATA_SOURCE_ID = QRY3.COMPLETING_PROCESS_ID
    AND ETL1.BATCH_RUN_ID = QRY3.BATCH_RUN_ID
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;

I can't follow your explanation, but this sounds like *combining overlapping periods*, which can be done with `SELECT NORMALIZE` — dnoeth, May 31 '23 at 12:18

user1874594 · Accepted Answer · 2023-06-01T10:57:05.330

Read your Query code - You need to do inner join with the ABC.ASSET_DEPENDENCY_CONFIG table on the asset ID to consider the dependency relationship between assets and processes.You need to use the MIN and MAX aggregation functions to determine the earliest start time (MIN(B.BATCH_START_TS)) and the latest completion time (MAX(B.BATCH_END_TS)) among all the processes related to an asset. That takes into account multiple processes for an asset. It identifies the earliest start time and the latest completion time among all the processes for a given asset. So your final code will be like this PS the original answer-er gave a sudo query but another person took some extra time to rewrite the original query with my suggestions which is below

            SELECT 
    MASTER.APPLICATION,
    MASTER.ASSET_NAME,
    MASTER.MODULE_NAME,
    MASTER.ASSET_TYP,
    CAST(CAL.CALENDAR_DAY_DT as format 'YYYY-MM-DD') + CAST(DEPEND.DATA_DELAY as interval DAY) AS DATA_DATE,
    MIN(ETL.BATCH_START_TS) AS START_TIME,
    MAX(ETL.BATCH_END_TS) AS FINAL_REFRESH_TIME,
        CASE 
                WHEN DEPEND.DATA_DELAY = -1 AND FINAL_REFRESH_TIME < cast(current_date as timestamp(0)) + INTERVAL '9' HOUR THEN 'Yes'
                WHEN DEPEND.DATA_DELAY = -2 AND FINAL_REFRESH_TIME < cast(current_date + INTERVAL '1' DAY as timestamp(0)) + INTERVAL '9' HOUR THEN 'Yes'
                ELSE 'No' END AS REFRESHED_BEFORE_9_AM, 
        CASE 
                WHEN DEPEND.DATA_DELAY = -1 AND FINAL_REFRESH_TIME < cast(current_date as timestamp(0)) + INTERVAL '12' HOUR THEN 'Yes'
                WHEN DEPEND.DATA_DELAY = -2 AND FINAL_REFRESH_TIME < cast(current_date + INTERVAL '1' DAY as timestamp(0)) + INTERVAL '12' HOUR THEN 'Yes'
                ELSE 'No' END AS REFRESHED_BEFORE_12_PM,
        CASE     
                WHEN FINAL_REFRESH_TIME < cast(current_date + INTERVAL '1' DAY as timestamp(0)) + INTERVAL '9' HOUR THEN 'No'
                ELSE 'Yes' END AS SOURCE_DATA_ISSUE,
        CASE 
                WHEN FINAL_REFRESH_TIME < cast(current_date + INTERVAL '1' DAY as timestamp(0)) + INTERVAL '9' HOUR THEN 'No' 
                ELSE 'Yes' END AS ETL_OPS_PROCESS_ISSUE,
        CASE
                WHEN FINAL_REFRESH_TIME < cast(current_date + INTERVAL '1' DAY as timestamp(0)) + INTERVAL '9' HOUR THEN 'No' 
                ELSE 'Yes' END AS PLATFORM_ISSUE,
        CASE 
                WHEN SOURCE_DATA_ISSUE = 'No' AND ETL_OPS_PROCESS_ISSUE = 'No' AND PLATFORM_ISSUE = 'No' THEN 'NA'
                WHEN SOURCE_DATA_ISSUE = 'Yes' OR ETL_OPS_PROCESS_ISSUE = 'Yes' OR PLATFORM_ISSUE = 'Yes' 
                    AND DEPEND.DATA_DELAY = -1 AND FINAL_REFRESH_TIME < cast(current_date as timestamp(0)) + INTERVAL '9' HOUR THEN 'Yes'
                WHEN SOURCE_DATA_ISSUE = 'Yes' OR ETL_OPS_PROCESS_ISSUE = 'Yes' OR PLATFORM_ISSUE = 'Yes'
                    AND DEPEND.DATA_DELAY = -2 AND FINAL_REFRESH_TIME < cast(current_date + INTERVAL '1' DAY as timestamp(0)) + INTERVAL '12' HOUR THEN 'Yes'
                ELSE 'No' END AS ISSUE_RESOLVED_SAME_DAY, 
        SUBSTRING((TRIM((CAST(((CAST(MAX(ETL.BATCH_END_TS) AS TIMESTAMP(0)) - CAST(MIN(ETL.BATCH_START_TS) AS TIMESTAMP(0))) DAY(4) TO SECOND) AS VARCHAR(50))))), 2, 9) AS DURATION,
        /* (TRIM((CAST(((CAST(MAX(ETL.BATCH_END_TS) AS TIMESTAMP(0)) - CAST(MIN(ETL.BATCH_START_TS) AS TIMESTAMP(0))) DAY(4) TO SECOND) AS VARCHAR(50))))) AS DURATION_TIMESTAMP,
        ((CAST(MAX(ETL.BATCH_END_TS) AS TIMESTAMP(0)) - MIN(ETL.BATCH_START_TS)) DAY(4) TO SECOND(4)) AS t1,
        (EXTRACT(DAY FROM t1) * (24 * 60 * 60) +
        EXTRACT(HOUR FROM t1) * (60 * 60) +
        EXTRACT(MINUTE FROM t1) * 60 +
        EXTRACT(SECOND FROM t1)) AS DURATION_SECONDS, */
        CASE ETL.BATCH_STATUS_CD
            WHEN 'C' THEN 'Completed'
            WHEN 'R' THEN 'Running'
            WHEN 'N' THEN 'Not Started'
            WHEN 'F' THEN 'Failed'
        END AS STATUS
    FROM
        ABC.ASSET_CONFIG MASTER
        INNER JOIN ABC.ASSET_DEPENDENCY_CONFIG DEPEND ON MASTER.ASSET_ID = DEPEND.ASSET_ID
        INNER JOIN ABC.ETL_BATCH_CONTROL ETL ON DEPEND.COMPLETING_PROCESS_ID = ETL.DATA_SOURCE_ID 
        INNER JOIN ABC.FISCAL_CALENDAR CAL ON 1=1 
        LEFT JOIN ABC.ETL_WORKFLOW_CONTROL WF ON WF.DATA_SOURCE_ID = ETL.DATA_SOURCE_ID AND WF.BATCH_RUN_ID = ETL.BATCH_RUN_ID
        LEFT JOIN ABC.NDW_ETL_SESSION_CONTROL SESS ON WF.WORKFLOW_RUN_ID = SESS.WORKFLOW_RUN_ID AND SESS.SESSION_INSTANCE_NM = DEPEND.COMPL_SESSION_NAME
        WHERE CAL.CALENDAR_DAY_DT BETWEEN '?ldt1' AND '?ldt2' 
        AND DEPEND.PRCS_CNTRL_STORE_TYP = 'NDW_PRCS_CNTRL_VIEWS.NDW_ETL_BATCH_CONTROL'
        AND CAST(DATA_DATE AS DATE) = CAST(ETL.INCREMENTAL_LOAD_START_TS AS DATE)
    GROUP BY
        --1,2,3,4,5,8,9,10,11,12,13
    MASTER.APPLICATION,
    MASTER.ASSET_NAME,
    MASTER.MODULE_NAME,
    MASTER.ASSET_TYP,
    DATA_DATE,
    DATA_DELAY,
    STATUS

Teradata Maximum process end time capture for multiple processes

1 Answers1