faster way to handle T-SQL Result , alternative to CURSOR

Question

i am working on a Report to generate

• All Fleet movement a temp table
• All Fleet Stops in a temp table set
• All Fleet idle in a temp table set

i have data like this

CREATE TABLE stops
    ([TripStopId] [int] IDENTITY(1,1) NOT NULL,[ObjectId] int, [DateFrom] varchar(23), [DateTo] varchar(23), [X] int, [Y] int, [ByIgnition] int, [BySpeed0] int, [BySpeed5] int, [BySpeed10] int, [BySpeed15] int, [ByCanSpeed0] int, [ByCanSpeed5] int, [ByCanSpeed10] int, [ByCanSpeed15] int, [GpsDistance] int, [CanDistance] int, [OdometerDistance] int, [Location] int)
;

INSERT INTO stops
    ([ObjectId], [DateFrom], [DateTo], [X], [Y], [ByIgnition], [BySpeed0], [BySpeed5], [BySpeed10], [BySpeed15], [ByCanSpeed0], [ByCanSpeed5], [ByCanSpeed10], [ByCanSpeed15], [GpsDistance], [CanDistance], [OdometerDistance], [Location])
VALUES
    (2729, '2016-01-27 06:21:51.320', '2016-01-27 06:22:27.070', 46.651984, 24.6881872, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0.0086855611934854, 0, 0, NULL),
    (2729, '2016-01-27 06:22:27.070', '2016-01-27 06:22:54.000', 46.652, 24.6881104, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0.0667479017095136, 0, 0, NULL),
    (2729, '2016-01-27 06:22:54.000', '2016-01-27 06:23:03.920', 46.651408, 24.68804, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0.0248830291828213, 0, 0, NULL),
    (2729, '2016-01-27 06:23:03.920', '2016-01-27 06:23:05.920', 46.6511616, 24.6880448, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0.00384351436414536, 0, 0, NULL),
    (2729, '2016-01-27 06:23:05.920', '2016-01-27 06:23:15.910', 46.651136, 24.6880704, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0.104523323509783, 0, 0, NULL),
    (2729, '2016-01-27 06:23:15.910', '2016-01-27 06:25:32.820', 46.6509376, 24.6889936, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1.57522938946324, 0, 0, NULL),
    (2729, '2016-01-27 06:25:32.820', '2016-01-27 06:25:53.810', 46.636304, 24.6873888, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0.0144023488655963, 0, 0, NULL),
    (2729, '2016-01-27 06:25:53.810', '2016-01-27 06:27:14.700', 46.6361632, 24.6874096, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0.00893761079401728, 0, 0, NULL),
    (2729, '2016-01-27 06:27:14.700', '2016-01-27 06:27:21.680', 46.6360864, 24.6873696, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0.0369525263958736, 0, 0, NULL),
    (2729, '2016-01-27 06:27:21.680', '2016-01-27 06:27:34.680', 46.6358208, 24.6871408, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0.0333843719279574, 0, 0, NULL),
    (2729, '2016-01-27 06:27:34.680', '2016-01-27 06:27:45.660', 46.6354912, 24.6871648, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0.114545761834751, 0, 0, NULL),
    (2729, '2016-01-27 06:27:45.660', '2016-01-27 06:35:00.480', 46.6346432, 24.6878496, 1, 1, 1, 1, 1, 0, 0, 0, 0, 7.52942986526197, 0, 0, NULL)
;

--

+----------+-------------------------+-------------------------+------------+------------+------------+----------+----------+-----------+-----------+-------------+-------------+--------------+--------------+---------------------+-------------+------------------+----------+
| ObjectId |        DateFrom         |         DateTo          |     X      |     Y      | ByIgnition | BySpeed0 | BySpeed5 | BySpeed10 | BySpeed15 | ByCanSpeed0 | ByCanSpeed5 | ByCanSpeed10 | ByCanSpeed15 |     GpsDistance     | CanDistance | OdometerDistance | Location |
+----------+-------------------------+-------------------------+------------+------------+------------+----------+----------+-----------+-----------+-------------+-------------+--------------+--------------+---------------------+-------------+------------------+----------+
|     2729 | 2016-01-27 06:21:51.320 | 2016-01-27 06:22:27.070 | 46.651984  | 24.6881872 |          1 |        0 |        0 |         0 |         0 |           0 |           0 |            0 |            0 | 0.0086855611934854  |           0 |                0 |          |
|     2729 | 2016-01-27 06:22:27.070 | 2016-01-27 06:22:54.000 | 46.652     | 24.6881104 |          1 |        1 |        1 |         0 |         0 |           0 |           0 |            0 |            0 | 0.0667479017095136  |           0 |                0 |          |
|     2729 | 2016-01-27 06:22:54.000 | 2016-01-27 06:23:03.920 | 46.651408  | 24.68804   |          1 |        1 |        1 |         1 |         1 |           0 |           0 |            0 |            0 | 0.0248830291828213  |           0 |                0 |          |
|     2729 | 2016-01-27 06:23:03.920 | 2016-01-27 06:23:05.920 | 46.6511616 | 24.6880448 |          1 |        1 |        1 |         0 |         0 |           0 |           0 |            0 |            0 | 0.00384351436414536 |           0 |                0 |          |
|     2729 | 2016-01-27 06:23:05.920 | 2016-01-27 06:23:15.910 | 46.651136  | 24.6880704 |          1 |        1 |        1 |         1 |         0 |           0 |           0 |            0 |            0 | 0.104523323509783   |           0 |                0 |          |
|     2729 | 2016-01-27 06:23:15.910 | 2016-01-27 06:25:32.820 | 46.6509376 | 24.6889936 |          1 |        1 |        1 |         1 |         1 |           0 |           0 |            0 |            0 | 1.57522938946324    |           0 |                0 |          |
|     2729 | 2016-01-27 06:25:32.820 | 2016-01-27 06:25:53.810 | 46.636304  | 24.6873888 |          1 |        1 |        1 |         1 |         0 |           0 |           0 |            0 |            0 | 0.0144023488655963  |           0 |                0 |          |
|     2729 | 2016-01-27 06:25:53.810 | 2016-01-27 06:27:14.700 | 46.6361632 | 24.6874096 |          1 |        0 |        0 |         0 |         0 |           0 |           0 |            0 |            0 | 0.00893761079401728 |           0 |                0 |          |
|     2729 | 2016-01-27 06:27:14.700 | 2016-01-27 06:27:21.680 | 46.6360864 | 24.6873696 |          1 |        1 |        1 |         0 |         0 |           0 |           0 |            0 |            0 | 0.0369525263958736  |           0 |                0 |          |
|     2729 | 2016-01-27 06:27:21.680 | 2016-01-27 06:27:34.680 | 46.6358208 | 24.6871408 |          1 |        1 |        1 |         1 |         1 |           0 |           0 |            0 |            0 | 0.0333843719279574  |           0 |                0 |          |
|     2729 | 2016-01-27 06:27:34.680 | 2016-01-27 06:27:45.660 | 46.6354912 | 24.6871648 |          1 |        1 |        1 |         1 |         0 |           0 |           0 |            0 |            0 | 0.114545761834751   |           0 |                0 |          |
|     2729 | 2016-01-27 06:27:45.660 | 2016-01-27 06:35:00.480 | 46.6346432 | 24.6878496 |          1 |        1 |        1 |         1 |         1 |           0 |           0 |            0 |            0 | 7.52942986526197    |           0 |                0 |          |
+----------+-------------------------+-------------------------+------------+------------+------------+----------+----------+-----------+-----------+-------------+-------------+--------------+--------------+---------------------+-------------+------------------+----------+

This data for each car move and its start X,Y, AND total Distance,

any way better than CURSOR as i believe CURSOR is SLOW, to make the following

• list all drives , and X,Y from the start drive and x,y of the next drive stop grouped by Objectid
• list all Stops , and its X,Y as Stop mean time between each 2 drive rows grouped by Objectid "for same object in reportgroup"

i am asking about best way as this report can have more than 1 million tripstop row

Thats the expected result i have by CURSOR , it take long time to output

    +----------+-------------------------+-------------------------+------------+------------+------------+------------+----------+----------+----------+----------+----------+--------+-----------+----------+-----------+----------+---------+--------------+--------------+------------+------------+---------------------+------------+----------+-------------+----------+
| ObjectId |        DateFrom         |         DateTo          |     X      |     Y      |     X2     |     Y2     | IsMoving | Distance | Location | AvgSpeed | DriverId | Driver | IButtonId | DateYear | DateMonth | DateWeek | DateDay | StartGeoName | StartAddress | EndGeoName | EndAddress | DeductionPointValue | PoiAtStart | PoiAtEnd | DrivingTime | StopTime |
+----------+-------------------------+-------------------------+------------+------------+------------+------------+----------+----------+----------+----------+----------+--------+-----------+----------+-----------+----------+---------+--------------+--------------+------------+------------+---------------------+------------+----------+-------------+----------+
|     2729 | 2016-01-27 06:21:51.320 | 2016-01-27 06:22:27.070 | 46.651984  | 24.6881872 | 46.652     | 24.6881104 |        0 | 0.01     |          | 0.87     | NULL     | NULL   | NULL      |     2016 |         1 |        5 |      27 |              | NULL         | NULL       | NULL       | 0.01                | NULL       | NULL     |           0 |    35750 |
|     2729 | 2016-01-27 06:22:27.070 | 2016-01-27 06:25:53.810 | 46.652     | 24.6881104 | 46.6361632 | 24.6874096 |        1 | 1.79     |          | 31.16    | NULL     | NULL   | NULL      |     2016 |         1 |        5 |      27 |              | NULL         | NULL       | NULL       | 1.8                 | NULL       | NULL     |      206740 |        0 |
|     2729 | 2016-01-27 06:25:53.810 | 2016-01-27 06:27:14.700 | 46.6361632 | 24.6874096 | 46.6360864 | 24.6873696 |        0 | 0.01     |          | 0.4      | NULL     | NULL   | NULL      |     2016 |         1 |        5 |      27 |              | NULL         | NULL       | NULL       | 1.81                | NULL       | NULL     |           0 |    80890 |
|     2729 | 2016-01-27 06:27:14.700 | 2016-01-27 06:35:00.480 | 46.6360864 | 24.6873696 | NULL       | NULL       |        1 | 7.71     |          | 59.62    | NULL     | NULL   | NULL      |     2016 |         1 |        5 |      27 |              | NULL         | NULL       | NULL       | 9.52                | NULL       | NULL     |      465780 |        0 |
+----------+-------------------------+-------------------------+------------+------------+------------+------------+----------+----------+----------+----------+----------+--------+-----------+----------+-----------+----------+---------+--------------+--------------+------------+------------+---------------------+------------+----------+-------------+----------+

Answer 1

This is the core query, where you can include your other calculated columns:

WITH CTE AS
(
SELECT S.*
FROM stops S
INNER JOIN
    (SELECT A.TripStopId AS TA, B.TripStopId AS TB
    FROM stops A
    INNER JOIN stops B ON A.ObjectId = B.ObjectId AND A.DateTo = B.DateFrom 
    WHERE A.BySpeed0 = 0 AND B.BySpeed0 = 1) T ON S.TripStopId = T.TA OR S.TripStopId = T.TB
)
SELECT CTE.TripStopId, CTE.ObjectId, CTE.DateFrom, CTE.DateTo, CTE.X, CTE.Y, S.X AS X2, S.Y AS Y2, CTE.BySpeed0 AS IsMoving
FROM CTE
LEFT JOIN stops S ON CTE.ObjectId = S.ObjectId
    AND ((CTE.BySpeed0 = 0 AND S.BySpeed0 = 1 AND CTE.DateTo = S.DateFrom)
        OR (CTE.BySpeed0 = 1 AND S.BySpeed0 = 0 AND CTE.DateTo <= S.DateFrom))

Answer 2

with cte as
(
select tripstopid,datefrom,dateto,x,y,byspeed0,
case when lag(byspeed0,1,'') over(order by datefrom,dateto) = byspeed0 then 0 else 1 end as ischange from stops
),cte1 as
(
select t.*,
 (select sum(ischange)  from cte where tripstopid <= t.tripstopid) groupno
from cte t
),cte3 as
(
select *,row_number() over(partition by groupno order by tripstopid) as rn1 from cte1 
),cte4 as
(
select *from cte3 
where rn1=1
)
select *from cte4 c1 left
join cte4 c2  on c1.groupno+1 =c2.groupno
order by c1.datefrom

Answer 3

I have not tested this to be sure that I have no errors, but something like this should do the trick. The idea is to group values together based on the BySpeed0 column then aggregate.

WITH AdjacentValues AS (
  /*
  This adds the prior BySpeed0 value to the row
  */
  SELECT
    ObjectID,
    DateFrom,
    DateTo,
    X,
    Y,
    [BySpeed0] AS 'IsMoving',
    LAG([BySpeed0]) OVER (PARTITION BY ObjectID ORDER BY DateFrom ASC) AS 'PriorValue'
  FROM
    stops
), GroupedResults AS (
  /*
  This will create a number that increments every time the current BySpeed0 value is not the same as the next one.
  */
  SELECT
    ObjectID,
    DateFrom,
    DateTo,
    X,
    Y,
    IsMoving,
    NextValue,
    SUM(IIF(IsMoving <> PriorValue, 1, 0)) OVER (PARTITION BY ObjectID ORDER BY DateFrom ASC) AS 'GroupingValue'
  FROM
    AdjacentValues
), AggregateResult AS (
  /*
  This will Select the minimum date and maximum date for a particular grouping
  */
  SELECT
    ObjectID,
    MIN(DateFrom) AS 'DateFrom',
    MAX(DateTo) AS 'DateTo'
  FROM
    AdjacentValues
  GROUP BY
    ObjectID,
    GroupingValue
)
  /*
  This will add the X/Y information
  */
  SELECT 
    R.*,
    A.X,
    A.Y,
    AA.X AS 'X2',
    AA.Y AS 'Y2'
  FROM
    AggregateResult R
  LEFT JOIN
    AdjacentValues A
  ON
    A.DateFrom = R.DateFrom
    AND A.ObjectID = R.ObjectID
  LEFT JOIN 
    AdjacentValues AA
  ON
    AA.DateTo = R.DateTo
    AND A.ObjectID = R.ObjectID

P.S. If you are using spatial data at all, it would only take minor adjustments to turn these coordinate pairs into geometry/geography lines.