# How to select a subset from a query result

I have a query which calculates the Time Difference and Distance Between the Next and Previous Row:

``````SELECT  * FROM (
SELECT
iVehicleMonitoringId AS PreviousId,
LEAD(iVehicleMonitoringId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentId,
iGeofenceId AS GeofenceId,
eEventCode as 'EventCode',
iAssetId AS AssetId,
LEAD(iAssetId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS PreviousAsset,
sDigitalInputValue AS PreviousIgnition,
LEAD(sDigitalInputValue, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentIgnition,
dtUTCDateTime AS StartDate,
LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS EndDate,
DATEDIFF(second, dtUTCDateTime, LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS DateDiffSeconds,
sptGeoLocaitonPoint.STDistance(LEAD(sptGeoLocaitonPoint, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS Distance

FROM VehicleMonitoringLog
WHERE dtUTCDateTime > @utcStartDate AND dtUTCDateTime < @utcEndDate
AND iAssetId in (Select iAssetID From #tblAssets)
) AS dt
ORDER BY dt.AssetId, dt.StartDate
``````

Which returns the following result Which is fine - Now I need to calculate the Total Distance and Time in between the selected section (Highlighted in Blue).

So basically it is the Total Distance and Total Time From eEventCode = 6 To eEventCode = 7. There can be more than one set with start event = 6 and end event = 7.

SQL Fiddle

How do I perform this operation?

EDIT Okay this is the new fiddle with a dataset: http://sqlfiddle.com/#!6/40a9e1

Now this data has two datasets where start eventcode = 6 and end eventcode = 7 Now if I execute this query - to get distance between two adjacent points:

``````SELECT
iVehicleMonitoringId AS PreviousId,
LEAD(iVehicleMonitoringId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentId,
iGeofenceId AS GeofenceId,
iAssetId,
dtUTCDateTime,
eEventCode as 'EventCode',
DATEDIFF(second, dtUTCDateTime, LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS DateDiffSeconds,
sptGeoLocaitonPoint.STDistance(LEAD(sptGeoLocaitonPoint, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS Distance

FROM VehicleMonitoringLog
WHERE
iAssetId = 6024 AND
dtUTCDateTime > N'2016-01-26 04:44:00' AND dtUTCDateTime <  N'2016-01-26 12:39:45'
Order by dtUTCDateTime
``````

The result should add all the Distances columns for each set. This query SUM all the distances between 6 and 7:

``````SELECT AssetId, [Event], GeofenceId
, DistanceCoveredK = SUM(DistanceCoveredK)
, TimeSpentDuringVisitSeconds
FROM (
SELECT AssetId = vm.iAssetId
, [Event] = c.eEventCode
, DistanceCoveredK = p.sptGeoLocaitonPoint.STDistance(
LEAD(p.sptGeoLocaitonPoint) OVER(PARTITION BY vm.iAssetId, vM.dtUTCDateTime ORDER BY c.dtUTCDateTime)
)
, GeofenceId = vm.iGeofenceId
, TimeSpentDuringVisitSeconds = DATEDIFF(SECOND, vm.dtUTCDateTime, c.dtUTCDateTime)
FROM VehicleMonitoringLog vm
CROSS APPLY (
SELECT top(1) eEventCode, dtUTCDateTime, sptGeoLocaitonPoint
FROM VehicleMonitoringLog WHERE iAssetId = vm.iAssetId AND dtUTCDateTime > vm.dtUTCDateTime AND eEventCode = 7
ORDER by dtUTCDateTime
) c
INNER JOIN VehicleMonitoringLog p ON p.iAssetId = vm.iAssetId AND p.dtUTCDateTime >= vm.dtUTCDateTime AND p.dtUTCDateTime <= c.dtUTCDateTime
WHERE vm.eEventCode = 6
) g
GROUP BY AssetId, [Event], GeofenceId, TimeSpentDuringVisitSeconds
``````

Output:

``````AssetId Event   GeofenceId  DistanceCoveredK    TimeSpentDuringVisitSeconds
6024    7       1015        172,642601234594    100
6024    7       1015        1210,11662866215    28393
``````

I changed the datatype of `sptGeoLocaitonPoint` in your SQL Fiddle from `int` to `geography`. New SQL Fiddle is here