Untitled

DECLARE @frondate AS DATETIME = '2074-09-28 17:09:34.000';
DECLARE @enddate AS DATETIME = '2024-09-28 21:42:47.000';

DECLARE @tbl AS TABLE (reason VARCHAR(MAX), unitname VARCHAR(MAX), start DATETIME, endtime DATETIME);

INSERT INTO @tbl
SELECT 
    reason,
    unitname,
    start_time.eventtime AS [Downtime start],
    end_time.stoptime AS [Downtime end]
FROM (
    SELECT 
        unitname, 
        uniqueid, 
        recipe, 
        failurevalue, 
        eventtime,
        ROW_NUMBER() OVER (PARTITION BY uniqueid ORDER BY eventtime) AS Row_Number
    FROM [10.232.0.32].Batchotistorytx.dbo.mfallure 
    WHERE unitname = 'PMX302' AND EVENTTIME BETWEEN @frondate AND @enddate
) AS start_time
LEFT JOIN (
    SELECT 
        uniqueid, 
        recipe, 
        eventtime AS [stoptime],
        ROW_NUMBER() OVER (PARTITION BY uniqueid ORDER BY eventtime) AS Row_Number
    FROM [10.232.0.32].BatchlistoryEx.dbo.BHFailureClear 
    WHERE EVENTTIME BETWEEN @frondate AND @enddate
) AS end_time
ON start_time.Row_Number = end_time.Row_Number AND start_time.uniqueid = end_time.uniqueid;

DECLARE @firstdowntime AS DATETIME;

SELECT TOP(1) @firstdowntime = start 
FROM @tbl 
ORDER BY start;

SELECT 
    reason,
    unitname,
    start,
    endtime,
    CASE 
        WHEN reason = 'OFF' THEN 'red'
        WHEN reason = 'ON' THEN 'green'
        ELSE 'unknown'  -- Optional: add a default case if needed
    END AS color
FROM (
    SELECT reason, 
           unitname, 
           start = @frondate, 
           endtime = @firstdowntime
    UNION ALL
    SELECT reason, 
           unitname, 
           endtime = (Runstart), 
           COALESCE(LEAD(start) OVER (ORDER BY start), @enddate) AS [RunstopTime]
    FROM @tbl
) AS t 
ORDER BY start;