How do I group these results? I have this query to calculate the Idle Time of a vehicle.

The Business Rule for Idle Time is - Ignition = 1 AND Speed = 0

So I have came up with the following Query.

DECLARE 
      @startDate DATETIME = N'2018-09-08',
      @endDate DATETIME = N'2018-09-10 23:59:59.997';

Select
  iAssetId,
  fSpeedKPH,
  dtUtcDateTime,
  eIgnition,
  IdleSeconds = (CASE WHEN eIgnition = 1 AND fSpeedKPH = 0 THEN DateDiffSeconds ELSE 0 END)

FROM (

    Select
        iAssetId,
        fSpeedKPH,
        dtUTCDateTime,
        eIgnition,
        DateDiffSeconds = DATEDIFF(SECOND, dtUTCDateTime, LEAD(dtUTCDateTime, 1)
            OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime))    
    FROM VehicleMonitoringLog

    WHERE 
        dtUTCDateTime BETWEEN @startDate AND @endDate
        AND iAssetId = 2
) A

ORDER BY 
    iAssetId, dtUTCDateTime


SQL FIDDLE - Link 

Now I need to GROUP the Idle Times into Single Times. So the result set should look like this:

------------------------------------------------------------------------
iAssetId   |  Start Idle Time  | End Idle Time | Idle Duration (Seconds)
------------------------------------------------------------------------




How do I achieve this?

EDIT
Expected Result

iAssetId  | Start Idle Time      | End Idle Time        | Idle Duration (seconds) 
-------------------------------------------------------------------
2         | 08-Sep-2018 00:04:51 | 08-Sep-2018 00:36:51 |  1986
2         | 08-Sep-2018 00:58:28 | 08-Sep-2018 03:50:29 |  10432
I think this gives you what you want.  

My solution uses several common table expression and a variety of Window functions.

There are commented out selects at the bottom of each common table expression that you can use to check individual results along the way.

My first step was to identify the start and end idle times.  I used LAG to check for the previous value of fSpeedKPH and eIgnition ordered by dtUtcDateTime.  If the current values equal the previous values, no change occurred, else a change occurred.  A new column called IsChanged is added to the results.

 ;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE 
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
--select * from _BaseData
    ,_BaseDataAndIsChanged  --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE 
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
    select * from _BaseDataAndIsChanged




My next step was to identify a grouping that I could use later to sum the IdleSeconds for each group.

;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE 
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
--select * from _BaseData
    ,_BaseDataAndIsChanged  --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE 
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
--  select * from _BaseDataAndIsChanged
    ,_BaseDataAndGroups     --cte to add groupno column to results 
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM _BaseDataAndIsChanged
            WHERE dtUTCDateTime &lt;= t.dtUTCDateTime
            ) groupno
    FROM _BaseDataAndIsChanged t
    )
select * from _BaseDataAndGroups




The last step is to sum IdleSeconds by groups and find the IdleStartTime and IdleEndTime This is also the final query.

;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE 
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
    --select * from _BaseData
    ,_BaseDataAndIsChanged --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE 
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
    --  select * from _BaseDataAndIsChanged
    ,_BaseDataAndGroups --cte to add groupno column to results 
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM _BaseDataAndIsChanged
            WHERE dtUTCDateTime &lt;= t.dtUTCDateTime
            ) groupno
    FROM _BaseDataAndIsChanged t
    )
    --select * from _BaseDataAndGroups
    ,BaseDataAndSums --cte to add SumIdleSeconds to results
AS (
    SELECT *
        ,sum(IdleSeconds) OVER (PARTITION BY groupno) AS IdleDurationSeconds
        ,FIRST_VALUE(dtUtcDateTime) OVER (
            PARTITION BY groupno ORDER BY dtUtcDateTime
            ) AS StartIdleTime
        ,LAST_VALUE(dtUtcDateTime) OVER (
            PARTITION BY groupno ORDER BY dtUtcDateTime RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING
            ) AS EndIdleTime
    FROM _BaseDataAndGroups
    )
SELECT iAssetId
    ,StartIdleTime
    ,EndIdleTime
    ,IdleDurationSeconds
FROM BaseDataAndSums
WHERE ischange = 1
    AND IdleDurationSeconds &gt; 0




| iAssetId | StartIdleTime           | EndIdleTime             | IdleDurationSeconds |
|----------|-------------------------|-------------------------|---------------------|
| 2        | 2018-09-08 00:04:51.000 | 2018-09-08 00:36:51.000 | 1986                |
| 2        | 2018-09-08 00:58:28.000 | 2018-09-08 03:50:29.000 | 10432               |