I have a GPS app that saves data to the following table:

CREATE TABLE [dbo].[T_Tracking]
(
    [id]          [int] IDENTITY(1,1) NOT NULL,
    [IMEI]        [nvarchar](50) NULL,
    [TrackTime]   [datetime] NULL,
    [Longitude]   [nvarchar](50) NULL,
    [Lattitude]   [nvarchar](50) NULL,
    [speed]       [float] NULL,
    [CarID]       [int] NULL,
    [Country]     [nvarchar](50) NULL,
    [City]        [nvarchar](50) NULL,
    [Area]        [nvarchar](50) NULL,
    [Street]      [nvarchar](50) NULL,
    [FullAddress] [nvarchar](150) NULL,
    [Distance]    [float] NULL
)

I need to get total time of parking and total time of driving per day:

Table Sample Data

What I have tried so far:

SELECT carid, SUM(DATEDIFF(minute,  a.trackold, a.TrackTime)) as LostTime ,TrackDay FROM (SELECT carid, TrackTime, LAG( TrackTime, 1, Null) OVER (PARTITION BY  carid  ORDER BY carid) as trackold ,
      CONVERT(date, TrackTime) as TrackDay
  FROM T_Tracking) a  
  group by carid,TrackDay

Query results:

the query almost correct but i need to get the column speed (where speed <3 for stop and speed>3 for drive ), e.g. car1 parking 10 hours driving 14 hours (parking speed < 3)

Thanks

it is gps System Insert a record every 1 minute per car the record contains the speed and the current Datetime the required to calculate how much the car has stoped per day and how much it was drive stop is not speed=0 it is speed <3 Expected

select 'Car1' as CarID, '01/01/2017' as TrackDay , 240 as StopTimeInMinutes , 300 as DriveTimeInMinutes

Edit sqlfiddle

http://www.sqlfiddle.com/#!6/cb633d Result

New Edit

      SELECT 
    carid
  , TrackTime
  , LAG( TrackTime, 1, Null) OVER (PARTITION BY  carid  ORDER BY carid) as trackold
  , CONVERT(date, TrackTime) as TrackDay
  , speed,DATEDIFF(minute, LAG( TrackTime, 1, Null) OVER (PARTITION BY  carid  ORDER BY carid),TrackTime)
FROM T_Tracking

Result

the problem is when the lag record on another day there must be a condition that the record and its lag on the same day