SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64) 
	Oct 18 2018 23:11:05 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)



   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 [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:1: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))
13 rows affected

select *
from dbo.table_main 


Sno
VehicleId
StartTime
EndTime


1
1001
15/02/2019 07:25:33
15/02/2019 17:25:33

2
1002
15/02/2019 06:12:52
15/02/2019 11:21:35

3
1003
15/02/2019 06:32:52
15/02/2019 11:21:35

4
1003
15/02/2019 13:01:21
15/02/2019 19:23:32


select b.vehicleid
      ,dateadd(s,sum(datediff(s,b.starttime,b.endtime)),cast('20180101' as datetime)) as elapsed_time
      ,count(*) as cnt_of_records
  from dbo.table_main a
  join dbo.table_detail b 
    on a.vehicleid=b.vehicleid
   and a.starttime<=b.starttime
   and a.endtime>=b.endtime
group by b.vehicleid
        ,a.sno   


vehicleid
elapsed_time
cnt_of_records


1001
01/01/2018 02:02:47
4

1002
01/01/2018 01:24:10
2

1003
01/01/2018 01:14:18
2

1003
01/01/2018 00:32:08
1
Sno	VehicleId	StartTime	EndTime
1	1001	15/02/2019 07:25:33	15/02/2019 17:25:33
2	1002	15/02/2019 06:12:52	15/02/2019 11:21:35
3	1003	15/02/2019 06:32:52	15/02/2019 11:21:35
4	1003	15/02/2019 13:01:21	15/02/2019 19:23:32
vehicleid	elapsed_time	cnt_of_records
1001	01/01/2018 02:02:47	4
1002	01/01/2018 01:24:10	2
1003	01/01/2018 01:14:18	2
1003	01/01/2018 00:32:08	1