SQL计算车辆离开/上路的总时间
SQL计算车辆离开/上路的总时间
提问于 2019-10-16 16:50:21
我希望我做得对，第一次发帖。下面的查询给出了一辆车在一段时间内的开/下车状态。
SELECT
VASH1.RecordID,
VASH1.VehicleAssetID,
VASH1.UpdateStatusID,
VASH1.UpdateStatusDate
FROM
dbo.VehicleAssetsStatusHistory VASH1
WHERE
VASH1.VehicleAssetID = '45793'
ORDER BY
VASH1.UpdateStatusDate`返回下列数据集：
Record Vehicle Update Update
ID Asset Status Status
ID ID Date
8720 45793 2 2016-12-21 12:31:51.893
16087 45793 2 2019-02-05 10:57:00.000
16711 45793 2 2019-03-27 14:15:00.000
16717 45793 2 2019-03-28 07:45:00.000
16956 45793 2 2019-04-10 20:00:00.000
17124 45793 2 2019-04-18 11:53:00.000
19592 45793 3 2019-06-06 08:58:00.000
19651 45793 3 2019-06-07 12:00:00.000
21030 45793 2 2019-06-12 15:00:00.000
20702 45793 3 2019-06-28 14:12:00.000
31711 45793 3 2019-07-18 08:27:00.000
43226 45793 2 2019-08-19 07:31:00.000
43274 45793 3 2019-08-19 14:30:00.000
43434 45793 2 2019-08-19 15:00:00.000
43349 45793 3 2019-08-21 09:00:00.000
43500 45793 3 2019-08-27 06:30:00.000
43526 45793 2 2019-08-27 10:00:00.000
43718 45793 2 2019-08-30 14:32:00.000
44214 45793 3 2019-09-11 11:30:00.000
44229 45793 2 2019-09-11 13:23:00.000
44854 45793 3 2019-09-24 12:30:00.000
44942 45793 2 2019-09-25 21:00:00.000
44988 45793 3 2019-09-27 07:00:00.000
45020 45793 2 2019-09-27 13:50:00.000
45496 45793 3 2019-10-08 12:00:00.000
45509 45793 2 2019-10-08 14:04:00.000更新状态ID 2= On Road和更新状态ID 3= Off Road。I想要计算和返回这辆车在路上和路上的总时间(以分钟为单位)(对于每个单独的查询，最好是单独的查询，因为它是较大查询的一部分)。
我最接近的是以下几分钟，在这里，我成功地计算了上路时间的运行总数。我接下来的步骤是聚合这个来给出总数，但是它失败了，因为我不能在select和select中使用聚合进行聚合。当时我希望弄清楚越野时间的总数：
SELECT
VASH1.RecordID,
VASH1.VehicleAssetID,
VASH1.UpdateStatusID,
VASH1.UpdateStatusDate,
CASE
--If last UpdateStatusDate (previous to this one) has an UpdateStatusID of 2 then it calculates the time between that last UpdateStatusDate and this one
WHEN (SELECT UpdateStatusID FROM Key2Live.dbo.VehicleAssetsStatusHistory VASH2 WHERE VASH2.VehicleAssetID=VASH1.VehicleAssetID AND VASH2.UpdateStatusDate =
(SELECT MAX(VASH3.UpdateStatusDate) FROM Key2Live.dbo.VehicleAssetsStatusHistory VASH3 WHERE VASH3.VehicleAssetID=VASH1.VehicleAssetID AND VASH3.UpdateStatusDate<VASH1.UpdateStatusDate))=2
THEN DATEDIFF(MINUTE, (SELECT MAX(VASH3.UpdateStatusDate) FROM Key2Live.dbo.VehicleAssetsStatusHistory VASH3 WHERE VASH3.VehicleAssetID=VASH1.VehicleAssetID AND VASH3.UpdateStatusDate<VASH1.UpdateStatusDate), VASH1.UpdateStatusDate)
ELSE 0
END AS 'MinutesOpen'
FROM
Key2Live.dbo.VehicleAssetsStatusHistory VASH1
WHERE
VASH1.VehicleAssetID = '45793'
ORDER BY
VASH1.UpdateStatusDate
Record Vehicle Update Update Minutes
Asset Status Status
ID ID ID Date Open
8720 45793 2 31:51.9 0
16087 45793 2 57:00.0 1117346
16711 45793 2 15:00.0 72198
16717 45793 2 45:00.0 1050
16956 45793 2 00:00.0 19455
17124 45793 2 53:00.0 11033
19592 45793 3 58:00.0 70385
19651 45793 3 00:00.0 0
21030 45793 2 00:00.0 0
20702 45793 3 12:00.0 22992
31711 45793 3 27:00.0 0
43226 45793 2 31:00.0 0
43274 45793 3 30:00.0 419
43434 45793 2 00:00.0 0
43349 45793 3 00:00.0 2520
43500 45793 3 30:00.0 0
43526 45793 2 00:00.0 0
43718 45793 2 32:00.0 4592
44214 45793 3 30:00.0 17098
44229 45793 2 23:00.0 0
44854 45793 3 30:00.0 18667
44942 45793 2 00:00.0 0
44988 45793 3 00:00.0 2040
45020 45793 2 50:00.0 0
45496 45793 3 00:00.0 15730
45509 45793 2 04:00.0 0，我是不是完全偏离了这个问题的轨道，如何用最简单的方法解决这个问题？
谢谢你提供的任何/所有帮助
回答 2
Stack Overflow用户
发布于 2019-10-21 15:35:12
对任何感兴趣的人来说，我找到的解决办法是：
WITH OffRoadDuration as
(
SELECT
VASH1.VehicleAssetID,
VASH1.UpdateStatusID,
DATEDIFF(MINUTE,VASH1.UpdateStatusDate, ISNULL(OA_VASH2.UpdateStatusDate,GETDATE()) ) AS OffRoadMinutes
FROM Key2Live.dbo.VehicleAssetsStatusHistory VASH1
OUTER APPLY
(SELECT TOP 1 * FROM Key2Live.dbo.VehicleAssetsStatusHistory VASH2
WHERE VASH2.VehicleAssetID = VASH1.VehicleAssetID AND VASH2.UpdateStatusDate > VASH1.UpdateStatusDate
ORDER BY VASH2.UpdateStatusDate ASC) AS OA_VASH2
WHERE
VASH1.VehicleAssetID='45793'
)
SELECT
ORD.VehicleAssetID,
ORD.UpdateStatusID,
SUM(ORD.OffRoadMinutes) AS [OffRoadMinutes],
ROUND(CONVERT(FLOAT,SUM(ORD.OffRoadMinutes))/1440,1) AS [OffRoadDays]
FROM
OffRoadDuration ORD
GROUP BY
ORD.VehicleAssetID, ORD.UpdateStatusID 谢谢你的帮助，非常感谢
Stack Overflow用户
发布于 2019-10-16 16:55:33
你想要一个带铅()的小组
Select vehicle, sum(case when
updateStatusId=2
then StatusWiseTimeDiff else
- StatusWiseTimeDiff end) from(
Select vehicle, UpdateStatusID,
Sum(Case when lead(statusid) over
(partition by
vehicle order by updatestatusdate
asc)<>statusid
Then
Date_Diff( Max(UpdateStatusDate),
Min(UpdateStatusDate))) as
StatusWiseTimeDiff from table
End from table
group by vehicle,
) 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58418165
复制相关文章
相似问题
腾讯云开发者
