Get total waiting time and number of rows from date intervals

Question

Post reply

Get total waiting time and number of rows from date intervals

Shanmuga Raj

Ten Centuries

Points: 1178
More actions
February 15, 2019 at 4:21 am

#409515

In the below given schema and data, we need to find the time waited for each vehicle and number of stops.

--- 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_Main] ([Sno], [VehicleId], [StartTime], [EndTime]) VALUES (3, N'1003  ', CAST(N'2019-02-15T06:32:52.0000000' AS DateTime2), CAST(N'2019-02-15T11:21:35.0000000' AS DateTime2))
INSERT [dbo].[Table_Main] ([Sno], [VehicleId], [StartTime], [EndTime]) VALUES (4, N'1003  ', CAST(N'2019-02-15T13:12:21.0000000' AS DateTime2), CAST(N'2019-02-15T19:23:32.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))

INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (7, N'1003  ', N'48   ', N'74 ', CAST(N'2019-02-15T07:13:13.0000000' AS DateTime2), CAST(N'2019-02-15T08:05:01.0000000' AS DateTime2)) INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (8, N'1003  ', N'48   ', N'74 ', CAST(N'2019-02-15T09:43:12.0000000' AS DateTime2), CAST(N'2019-02-15T10:05:42.0000000' AS DateTime2)) INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (9, N'1003  ', N'48   ', N'74 ', CAST(N'2019-02-15T14:13:13.0000000' AS DateTime2), CAST(N'2019-02-15T14:45:21.0000000' AS DateTime2))

My expected result is

VehicleId ElapsedTime NoOfRecords 1001   02:02:47:000 4 1002   01:24:10:000 2
1003 01:04:43:000 2
1003 00:32:10:000 1

ie :Total time waited for vehicle 1 is Total Time : 02:03:20 No of Records :4

this way i need SQL Query for all the vehicles based on the start time . for a given date, we can have mulitple start time and this has to be checked against inbetween of end time in the detail table

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63198 More actions · Answer 1

pietlinden

SSC Guru

Points: 63198

February 15, 2019 at 6:37 am

#2022142

What did you try? A DATEDIFF to get an individual record's wait and then sum that?

Jonathan AC Roberts SSCoach Points: 17601 More actions · Answer 2

To get the seconds waited you can use this:
select VehicleId, SUM(DATEDIFF(ss,StartTime,EndTime)) Seconds, Count(*) Count from [dbo].[Table_Detail] group by VehicleId
All you need to do then in work out how to convert seconds to hh:mm:ss format. (It might help if you put it in a cte)
This might help with that: https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 3

Jonathan AC Roberts - Saturday, February 16, 2019 4:44 PM

To get the seconds waited you can use this:
select VehicleId, SUM(DATEDIFF(ss,StartTime,EndTime)) Seconds, Count(*) Count from [dbo].[Table_Detail] group by VehicleId
All you need to do then in work out how to convert seconds to hh:mm:ss format. (It might help if you put it in a cte)
This might help with that: https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql

I am not getting the expected result. can u also lookin the vehicle 3 has came twice on same date.
want it to be also splitted

pietlinden SSC Guru Points: 63198 More actions · Answer 4

Shanmuga Raj - Friday, February 15, 2019 4:21 AM

In the below given schema and data, we need to find the time waited for each vehicle and number of stops.

--- 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_Main] ([Sno], [VehicleId], [StartTime], [EndTime]) VALUES (3, N'1003  ', CAST(N'2019-02-15T06:32:52.0000000' AS DateTime2), CAST(N'2019-02-15T11:21:35.0000000' AS DateTime2))
INSERT [dbo].[Table_Main] ([Sno], [VehicleId], [StartTime], [EndTime]) VALUES (4, N'1003  ', CAST(N'2019-02-15T13:12:21.0000000' AS DateTime2), CAST(N'2019-02-15T19:23:32.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))

INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (7, N'1003  ', N'48   ', N'74 ', CAST(N'2019-02-15T07:13:13.0000000' AS DateTime2), CAST(N'2019-02-15T08:05:01.0000000' AS DateTime2)) INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (8, N'1003  ', N'48   ', N'74 ', CAST(N'2019-02-15T09:43:12.0000000' AS DateTime2), CAST(N'2019-02-15T10:05:42.0000000' AS DateTime2)) INSERT [dbo].[Table_Detail] ([Sno], [VehicleId], [DriverId], [LocationId], [StartTime], [EndTime]) VALUES (9, N'1003  ', N'48   ', N'74 ', CAST(N'2019-02-15T14:13:13.0000000' AS DateTime2), CAST(N'2019-02-15T14:45:21.0000000' AS DateTime2))

My expected result is

VehicleId ElapsedTime NoOfRecords 1001   02:02:47:000 4 1002   01:24:10:000 2
1003 01:04:43:000 2
1003 00:32:10:000 1

ie :Total time waited for vehicle 1 is Total Time : 02:03:20 No of Records :4

this way i need SQL Query for all the vehicles based on the start time . for a given date, we can have mulitple start time and this has to be checked against inbetween of end time in the detail table

"Detail" isn't super descriptive. What do the parent table records represent? The beginning and end of a trip?
What about the child table records? Stops along the way?