admin管理员组

文章数量:1316538

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 edited Jan 29 at 11:04 jarlh 44.8k8 gold badges50 silver badges67 bronze badges asked Jan 29 at 10:38 spolalespolale 747 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

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

本文标签： sql serverSQL obtain timestamps for end datetime row 1 and start timestamp row 2Stack Overflow

版权声明：本文标题：sql server - SQL obtain timestamps for end datetime row 1 and start timestamp row 2 - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742001689a2411141.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。