d, Sql Server - rextester

Language: Layout:

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

SELECT vehicleid, 
       CONVERT(VARCHAR, Dateadd(ms, te, 0), 114) AS ElapsedTime, 
       ct                                        AS NoOfRecords 
FROM   (SELECT vehicleid, 
               Sno,
               Sum(dms) te, 
               Count(*) ct 
        FROM   (SELECT d.vehicleid, m.Sno,
                       Datediff(ms, d.starttime, d.endtime) dms 
                FROM   [table_detail] d
				inner join [Table_Main] m on d.VehicleId=m.VehicleId 
				where m.starttime<=d.starttime
								and m.endtime>=d.endtime
				
				)t 
        GROUP  BY vehicleid, sno) t1

​x
 
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: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))
​
​
SELECT vehicleid, 
       CONVERT(VARCHAR, Dateadd(ms, te, 0), 114) AS ElapsedTime, 
       ct                                        AS NoOfRecords 
FROM   (SELECT vehicleid, 
               Sno,

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 13, absolute service time: 0,23 sec, absolute service time: 0,24 sec