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.
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
1 Answer
Reset to default 2You 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, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论