sql - 从日期间隔获取总等待时间和行数

Question

在下面给出的模式和数据中，我们需要找到每辆车的等待时间和停靠次数。

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

即:WAITING车辆 1 的总时间是总时间:02:03:20记录数:4

这样我需要所有车辆的 SQL 查询

Answer 1

这是你需要的吗？

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)用于将此日期时间值显示为时间。