sql server - SQL obtain timestamps for end datetime row 1 and start timestamp row 2 - Stack Overflow

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

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.

• sql
• sql-server

Share Improve this question Follow edited Jan 29 at 11:04 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Jan 29 at 10:38 spolalespolale 7477 bronze badges 2

• MySQL categorically != SQL Server...what is your actual database? – Tim Biegeleisen Commented Jan 29 at 10:40
• MicrosoftSQL, sorry I mistakenly added mysql tag – spolale Commented Jan 29 at 10:47

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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