SQL obtain timestamps for end datetime row 1 and start timestamp row 2

Question

I have the following table

SFC	WORK_CENTER	OPERATION	StartTime	EndTime	Duration
831260880700.00.0002	PICKG01	301	2023-09-07 12:55:10	2023-09-07 12:58:29	199
831260880700.00.0002	CELL29	314	2023-09-07 13:05:08	2023-09-07 13:13:34	506

And I want to change it so that I will have an additional row with the time passed between the endtime of pickg01 row and start time of cell29 row.

It should look like this:

SFC	WORK_CENTER	OPERATION	StartTime	EndTime	Duration
831260880700.00.0002	PICKG01	301	2023-09-07 12:55:10	2023-09-07 12:58:29	199
831260880700.00.0002	PICKG01_gap	000	2023-09-07 12:58:29	2023-09-07 13:05:08	399
831260880700.00.0002	CELL29	314	2023-09-07 13:05:08	2023-09-07 13:13:34	506

I can't help but find incredibly intricate ways such as creating different queries of the table and concatenating them together, is there a simpler way? Thanks.

Answer 1

You can try the following query:

    WITH base AS (
    
    SELECT 
        SFC, WORK_CENTER, OPERATION, StartTime, EndTime, Duration 
    FROM work_operations

    UNION ALL
        
    SELECT 
        SFC,
        WORK_CENTER + '_gap' AS WORK_CENTER,
        '000' AS OPERATION,
        LAG(EndTime) OVER (PARTITION BY SFC ORDER BY StartTime) AS StartTime,
        StartTime AS EndTime,
        DATEDIFF(SECOND, LAG(EndTime) OVER (PARTITION BY SFC ORDER BY StartTime), StartTime) AS Duration
    FROM work_operations)

SELECT * FROM base 
WHERE StartTime IS NOT NULL
ORDER BY StartTime;

Working example here: https://dbfiddle.uk/vttzhNOn