Untitled

DECLARE @fromdate AS DATETIME = '2024-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, color VARCHAR(10));

-- Insert into @tbl
INSERT INTO @tbl
SELECT
    reason = 'OFF',
    start_time.unitname,
    start_time.eventtime AS [Downtime Start],
    end_time.stoptime AS [Downtime End],
    CASE
        WHEN reason = 'ON' THEN 'green'
        ELSE 'red'
    END AS color
FROM (
    SELECT 
        unitname,
        Uniqueid,
        recipe,
        failurevalue,
        eventtime,
        ROW_NUMBER() OVER (PARTITION BY Uniqueid ORDER BY eventtime) AS Row_Number
    FROM [10.232.0.32].BatchHistoryEx.dbo.BHFailure
    WHERE unitname = 'MX302' 
      AND EVENTTIME BETWEEN @fromdate 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].BatchHistoryEx.dbo.BHFailureClear
    WHERE EVENTTIME BETWEEN @fromdate 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;

-- Get the first downtime
SELECT TOP(1) @firstdowntime = start 
FROM @tbl 
ORDER BY start;

-- Create the final output
SELECT
    reason = 'ON', 
    unitname = 'MX302', 
    start = @fromdate, 
    endtime = @firstdowntime, 
    color
FROM @tbl
UNION ALL
SELECT
    reason = 'ON',
    unitname,
    endtime AS [RunStart], 
    COALESCE(LEAD(start) OVER (ORDER BY start), @enddate) AS [RunStopTime],
    color
FROM @tbl 
ORDER BY start;