Teradata Labelling correct status involving multiple processes

Question

I need help with determining correct STATUS of an asset, start & final refresh time correctly as one asset can be tagged to multiple processes having different status.

START TIME: It should give minimum start time among the processes under that asset which starts earliest and only if none of the processes start under an asset it should be NULL

FINAL_REFRESH_TIME: It should give final job completion time among all processes under an asset only if all 3 processes completed, else it should show as NULL

STATUS: Only one status per asset depends on underlying processes and status should show as per given chart

Case 1: Status should show as 'R' or Running
Asset_ID    Process_Id  Status
10080   500 C
10080   501 C
10080   502 R

Case 2: Status should show as 'C' or Completed
Asseet_id   Process_Id  Status
10080   500 C
10080   501 C
10080   502 C

Case 3: Status should show as 'Waiting'
Asseet_id   Process_Id  Status
10080   500 C
10080   501 C
10080   502 N

Case 4: Status should show as 'N' or 'Not Started'
Asseet_id   Process_Id  Status
10080   500 N
10080   501 N
10080   502 N

I have prepared below query but this is giving multiple rows for an asset id 10080 but there should be only one row per asset:

            SELECT
    MODULE_BASED.APPLICATION,
    MODULE_BASED.ASSET_TYP,
    MODULE_BASED.ASSET_NAME,
    MODULE_BASED.MODULE_NAME,
    MODULE_BASED.DATA_DATE,
    CASE 
        WHEN MODULE_BASED.STATUS IN ('Completed','In Progress','Not Started') AND MAX_STATUS = 'In Progress' THEN 'In Progress'
        WHEN MODULE_BASED.STATUS IN ('Completed','In Progress','Not Started') AND MAX_STATUS = 'Not Started' THEN 'In Progress'
        WHEN MODULE_BASED.STATUS IN ('Completed') AND MAX_STATUS = 'Completed' THEN 'Completed'
        WHEN MODULE_BASED.STATUS IN ('Not Started') AND MAX_STATUS = 'Not Started' THEN 'Not Started'
        WHEN MODULE_BASED.STATUS IN ('In Progress') AND MAX_STATUS = 'In Progress' THEN 'In Progress'
        WHEN MODULE_BASED.STATUS IN ('Completed','Not Started') AND MAX_STATUS = 'Not Started' THEN 'Waiting'
        WHEN MODULE_BASED.STATUS IN ('Failed','Not Started','In Progress','Completed') AND MAX_STATUS = 'Failed' THEN 'Failed'
    END AS STATUS, 
    MIN(MODULE_BASED.START_TIME) AS START_TIME,
    MAX(MODULE_BASED.END_TIME) AS END_TIME,
    SUBSTRING((TRIM((CAST(((CAST(MAX(MODULE_BASED.END_TIME) AS TIMESTAMP(0)) - CAST(MIN(MODULE_BASED.START_TIME) AS TIMESTAMP(0))) DAY(4) TO SECOND) AS VARCHAR(50))))),2,9) AS DURATION
    FROM
    (
    SELECT 
    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)) - 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 Required_Output,
    CASE WHEN ETL1.PROCESS_RUN_STATUS_CD = 'S' THEN 'Completed'
        WHEN ETL1.PROCESS_RUN_STATUS_CD = 'R' THEN 'In Progress'
        WHEN ETL1.PROCESS_RUN_STATUS_CD = 'N' THEN 'Not Started'
        WHEN ETL1.PROCESS_RUN_STATUS_CD = 'F' THEN 'Failed'
    END AS STATUS
    FROM
    (
    SELECT 
    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.RUN_ID) OVER(PARTITION BY QRY2.APPLICATION,QRY2.ASSET_TYP,QRY2.ASSET_NAME,QRY2.MODULE_NAME,QRY2.COMPLETING_PROCESS_ID,QRY2.DATA_DATE)AS RUN_ID
    FROM
    (
    SELECT 
    QRY1.APPLICATION,
    QRY1.ASSET_TYP,
    QRY1.ASSET_NAME,
    QRY1.MODULE_NAME,
    QRY1.COMPLETING_PROCESS_ID,
    QRY1.DATA_DATE,
    ETL.PROCESS_RUN_STATUS_CD,
    ETL.PROCESS_START_TS AS START_TIME
    ,ETL.PROCESS_END_TS AS END_TIME
    ,ETL.RUN_ID
    FROM
    (
    select MASTER.ASSET_ID,
    MASTER.APPLICATION,
    MASTER.ASSET_TYP,
    MASTER.ASSET_NAME, 
    MASTER.MODULE_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 
    NDW_EBI_DMR_DEV_TABLES.ASSET_CONFIG MASTER
    INNER JOIN
    NDW_EBI_DMR_DEV_TABLES.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-06-02' AND '2023-06-02' 
    where
    DEPEND.PRCS_CNTRL_STORE_TYP = 'NDW_EBI_DMR_SHARED_VIEWS.PROCESS_CONTROL'
    AND MASTER.ASSET_ID = 10080
    GROUP BY 1,2,3,4,5,6,7,8,9
    )QRY1
    LEFT JOIN
    NDW_EBI_DMR_SHARED_VIEWS.PROCESS_CONTROL ETL
    ON
    ETL.PROCESS_ID = QRY1.COMPLETING_PROCESS_ID
    AND ETL.LOAD_START_TS = QRY1.DATA_DATE
    )QRY2
    )QRY3
    INNER JOIN
    NDW_EBI_DMR_SHARED_VIEWS.PROCESS_CONTROL ETL1
    ON
    ETL1.PROCESS_ID = QRY3.COMPLETING_PROCESS_ID
    AND ETL1.RUN_ID = QRY3.RUN_ID
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13
    ) MODULE_BASED
    GROUP BY 1,2,3,4,5,7;

Output i am getting:

  Enterprise,Care Data Assets (Xfinity 2.0),NSD,Business Critical,2023-06-01,2023-06-01 06:00:34,2023-06-02 06:23:33, 00:22:59, Completed
  Enterprise,Care Data Assets (Xfinity 2.0),NSD,Business Critical,2023-06-01,2023-06-02 07:36:20,2023-06-02 18:00:34, 08:11:16, Running

Output i want:

  Enterprise,Care Data Assets (Xfinity 2.0),NSD,Business Critical,2023-06-01,2023-06-01 06:00:34,2023-06-02 18:00:34, 18:00:00, Running

Thanks,

Debasis

Your CASE expression and the example don't align. But your description sounds like you may want to first use windowed LAST_VALUE to obtain the status for a set of rows, and then apply GROUP BY in an outer query. Alternatively it may be possible to "encode" the statuses for individual lines and "decode" or combine to yield the final status from that without a window function, e.g. MAX(CASE WHEN ETL.PROCESS_RUN_STATUS_CD = 'N' THEN 1 ELSE 0 END) would check whether there was at least one 'N' status. — Fred, Jun 02 '23 at 23:03
I have updated my code, seems like there is some issue with the outer most group by as this failing. — Debasis, Jun 03 '23 at 03:28
Your outer query now references MAX_STATUS but that column / expression is undefined. If you are seeing an error / failure, generally it is best to post the exact error message. — Fred, Jun 05 '23 at 14:39

Teradata Labelling correct status involving multiple processes

0 Answers0