前一行停车时间为零
前一行停车时间为零
提问于 2023-01-26 10:40:00
我需要计算一个采摘事件的真实时间，下面是一个挑选工作的扫描审核，总时间是18分钟，但真正的时间是2.09分钟。所以我需要在公园旗之后的下一排的时间为0
任何想法都值得赞赏
Create table #packpark
( Shipment bigint,
Shipmentid Nvarchar (20),
CreatedUser Nvarchar (20),
TableName Nvarchar (50),
CreatedDate Datetime ,
AuditCode Nvarchar (50),
QtyPicked decimal (13,2),
SystemType Nvarchar (20)
)
INSERT INTO #packpark
VALUES
('849975', '', 'Jon', 'bdt_PickedItems', '2023-01-26 10:09:00.723', 'Picking Started', '0.00000', 'S'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:09:28.833', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:09:29.380', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:09:29.910', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:09:43.147', '', '1.00000', 'N'),
('849975', '', 'Jon', 'bdt_PickedItems', '2023-01-26 10:09:48.630', 'Picking Parked', '0.00000', 'P'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:15:11.587', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:15:12.290', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:15:13.257', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:15:21.570', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:15:37.710', '', '1.00000', 'N'),
('849975', '', 'Jon', 'bdt_PickedItems', '2023-01-26 10:15:43.070', 'Picking Parked', '0.00000', 'P'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:26:23.343', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:26:57.907', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:27:12.780', '', '1.00000', 'N'),
('849975', 'SH00216747', 'Jon', 'Picking', '2023-01-26 10:27:13.657', '', '1.00000', 'N'),
('849975', '', 'Jon', 'bdt_PickedItems', '2023-01-26 10:50:09.320', 'Picking Parked', '0.00000', 'P')
Select
Shipment
,ShipmentId
,CreatedUser
,Tablename
,CreatedDate
,CONVERT(VARCHAR(15), CONVERT(TIME , CreatedDate - LAG(CreatedDate) OVER (ORDER BY CreatedDate)))as time_hhmm
,AuditCode
,QtyPicked
,SystemType
from
#packpark
drop table #packpark
回答 1
Database Administration用户
发布于 2023-01-26 13:03:11
你指的是这样的东西，还是你想要看到一个总持续时间的总和？
SELECT
Shipment,
Shipmentid,
CreatedUser,
TableName,
CreatedDate,
CASE WHEN LAG(AuditCode) OVER (ORDER BY CreatedDate) = 'Picking Parked' THEN
'00:00:00.000000'
ELSE
CONVERT(
VARCHAR(15),
CONVERT(
TIME,
CreatedDate
- LAG(CreatedDate) OVER (ORDER BY CreatedDate)
)
)
END AS time_hhmm,
AuditCode,
QtyPicked,
SystemType
FROM #packpark;页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/322614
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号