SQL 计算车辆离开/上路的总时间

Question

我希望我第一次发帖时做对了。 以下查询给出了一段时间内单个车辆的行驶/越野状态

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 = 在路上，更新状态 ID 3 = 越野。我想计算并返回这辆车离开道路和在路上的总时间(以分钟为单位((每个值的单个值，首选单独的查询，因为它是较大查询的一部分(。

我最接近的是以下内容，我设法以分钟为单位计算了 On Road 时间的运行总数。 我的下一步是聚合它以给出总数，但它失败了，因为我无法在选择和子选择中聚合聚合。 然后我希望弄清楚越野总时间：

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

我在这里是否完全偏离了如何以最简单的方式解决这个问题的轨道？

感谢您提供的任何/所有帮助

Answer 1

对于任何感兴趣的人，我找到的解决方案是：

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 

感谢您的所有帮助，非常感谢

Answer 2

你想要一个带有 lead(( 的组

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, 
)