Untitled

WITH Query1 AS (
    SELECT 
    A.A_ASSET_ID,
    A.EndDowntime AS StartDowntime, 
    SYSDATE AS EndDowntime,
    (SYSDATE - A.EndDowntime) * 24 * 3600 AS DOWNTIME,  
    TO_CHAR(TRUNC((SYSDATE - A.EndDowntime) * 24), 'FM999') || ' jam ' ||
    TO_CHAR(TRUNC(MOD((SYSDATE - A.EndDowntime) * 24 * 60, 60)), 'FM99') || ' menit ' ||
    TO_CHAR(TRUNC(MOD((SYSDATE - A.EndDowntime) * 24 * 3600, 60)), 'FM99') || ' detik' AS DOWNTIME_FORMAT
FROM (
    SELECT 
        A_ASSET_ID,
        DATETRX AS EndDowntime
    FROM 
        A_ASSET_LOG 
    WHERE 
        A_ASSET_ID = 1000129 
        AND DATETRX BETWEEN TO_DATE('01-OCT-2024', 'DD-MON-YYYY') AND SYSDATE
) A
WHERE 
    (SYSDATE - A.EndDowntime) * 24 * 3600 > 300 
    AND ROWNUM = 1 
ORDER BY 
    A.EndDowntime DESC
), 
Query2 AS (
    SELECT A.A_ASSET_ID, A.StartDowntime, A.EndDowntime, A.CYCLETIME AS Downtime,
    TRUNC(A.CYCLETIME / 86400) || ' hari ' || 
    TRUNC(MOD(A.CYCLETIME, 86400) / 3600) || ' jam ' || 
    TRUNC(MOD(A.CYCLETIME, 3600) / 60) || ' menit ' || 
    MOD(A.CYCLETIME, 60) || ' detik' AS Downtime_Format
FROM (SELECT 
        A_ASSET_ID,
        LEAD(DATETRX) OVER (ORDER BY DATETRX DESC) AS StartDowntime,
        NVL(DATETRX, SYSDATE) AS EndDowntime,
        CYCLETIME
    FROM 
        A_ASSET_LOG 
    WHERE A_ASSET_ID = 1000129 AND DATETRX BETWEEN TO_DATE('01-OCT-2024', 'DD-MON-YYYY') AND SYSDATE
) A
WHERE A.CYCLETIME > 300
ORDER BY 
    A.EndDowntime DESC
)
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2