sql - 从日期间隔获取总等待时间和行数
问题描述
在下面给出的模式和数据中,我们需要找到每辆车的等待时间和停靠次数。
--- CREATE TABLE
CREATE TABLE [dbo].[Table_Detail](
[Sno] [int] NOT NULL,
[VehicleId] [nchar](10) NULL,
[DriverId] [nchar](10) NULL,
[LocationId] [nchar](10) NULL,
[StartTime] [datetime2](7) NULL,
[EndTime] [datetime2](7) NULL
)
CREATE TABLE [dbo].[Table_Main](
[Sno] [int] NOT NULL,
[VehicleId] [nchar](10) NULL,
[StartTime] [datetime2](7) NULL,
[EndTime] [datetime2](7) NULL)
-- INSERT DATA
INSERT [dbo].[Table_Main] ([Sno], [VehicleId], [StartTime], [EndTime]) VALUES (1, N'1001 ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), CAST(N'2019-02-15T17:25:33.0000000' AS DateTime2))
INSERT [dbo].[Table_Main] ([Sno], [VehicleId], [StartTime], [EndTime]) VALUES (2, N'1002 ', CAST(N'2019-02-15T06:12:52.0000000' AS DateTime2), CAST(N'2019-02-15T11:21:35.0000000' AS DateTime2))
INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (1, N'1001 ', N'34 ', N'53 ', CAST(N'2019-02-15T07:55:32.0000000' AS DateTime2), CAST(N'2019-02-15T08:15:23.0000000' AS DateTime2))
INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (2, N'1002 ', N'23 ', N'65 ', CAST(N'2019-02-15T07:11:33.0000000' AS DateTime2), CAST(N'2019-02-15T07:45:33.0000000' AS DateTime2))
INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (3, N'1001 ', N'34 ', N'53 ', CAST(N'2019-02-15T09:22:52.0000000' AS DateTime2), CAST(N'2019-02-15T09:45:59.0000000' AS DateTime2))
INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (4, N'1002 ', N'23 ', N'65 ', CAST(N'2019-02-15T10:25:13.0000000' AS DateTime2), CAST(N'2019-02-15T11:15:23.0000000' AS DateTime2))
INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (5, N'1001 ', N'34 ', N'53 ', CAST(N'2019-02-15T11:25:36.0000000' AS DateTime2), CAST(N'2019-02-15T12:35:37.0000000' AS DateTime2))
INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (6, N'1001 ', N'34 ', N'53 ', CAST(N'2019-02-15T15:15:33.0000000' AS DateTime2), CAST(N'2019-02-15T15:25:21.0000000' AS DateTime2))
我的预期结果是
[Sno]、[VehicleId]、[StartTime]、[EndTime]、[等待的总时间]、[记录数]
StartTime EndTime
2019-02-15 07:55:32.0000000 2019-02-15 08:15:23.0000000 = 00:20:10
2019-02-15 09:22:52.0000000 2019-02-15 09:45:59.0000000 = 00:23:08
2019-02-15 11:25:36.0000000 2019-02-15 12:35:37.0000000 = 01:10:01
2019-02-15 15:15:33.0000000 2019-02-15 15:25:21.0000000 = 00:10:14
Total Time : 02:03:20
No of Records :4
即:等待车辆 1 的总时间是总时间:02:03:20 记录数:4
这样我需要对所有车辆进行 SQL 查询
解决方案
这是你需要的吗?
SELECT
VehicleId = T.VehicleId,
ElapsedTime = CONVERT(
VARCHAR(50),
DATEADD(
MILLISECOND,
SUM(DATEDIFF(SECOND, T.StartTime, T.EndTime)) * 1000,
0),
114),
NoOfRecords = COUNT(1)
FROM
[dbo].[Table_Detail] AS T
GROUP BY
T.VehicleId
结果:
VehicleId ElapsedTime NoOfRecords
1001 02:02:47:000 4
1002 01:24:10:000 2
DATEDIFF(SECOND, T.StartTime, T.EndTime)
返回每个间隔之间的秒数。SUM()
只需为特定车辆添加所有这些秒数(从GROUP BY
列列表中)。
DATEADD(MILLISECOND, <seconds> * 1000, 0)
用于将总秒数的总和转换为一个值DATETIME
,然后CONVERT(VARCHAR(50), <datetime>, 14)
用于将此日期时间值显示为时间。
推荐阅读
- java - 返回所有满足条件的字符串值
- apache-spark - 将复杂的 PSQL 查询转换为 SparkSQL 时出现不清楚的解析错误
- rstudio - 使用子文件夹发布到域时生成的链接在 Hugo/Blogdown 中断开
- powershell - Powershell -lt 和 -gt 给出与预期相反的结果
- php - 从 PHP 检查 HTML
- php - PHP PDOStatement 正确的占位符
- node.js - socket.io 客户端如何找到 /socket.io/socket.io.js
- hibernate - JPA/Hibernate 一对一额外列
- terraform - AWS 和 Terraform - cidrsubnet 函数中的 netnum
- c# - 如何获取 API 项目的用户设备的 GPS 位置?