SQL Server – How to Select a Subset from a Query Result

sql serversql-server-2012subquery

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

enter image description here

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

enter image description here

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.

enter image description here

Best Answer

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

Related Question