title: faster way to handle T-SQL Result , alternative to CURSOR
body: <p>i am working on a  Report  to generate </p>
  
  <ul>
  <li>All Fleet movement  a temp table</li>
  <li>All Fleet Stops in a temp table set</li>
  <li>All Fleet idle in a temp table set</li>
  </ul>
  
  <p>i have data like this </p>
  
  <pre><code>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)
  ;
  </code></pre>
  
  <p>--</p>
  
  <pre><code>+----------+-------------------------+-------------------------+------------+------------+------------+----------+----------+-----------+-----------+-------------+-------------+--------------+--------------+---------------------+-------------+------------------+----------+
  | 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 |          |
  +----------+-------------------------+-------------------------+------------+------------+------------+----------+----------+-----------+-----------+-------------+-------------+--------------+--------------+---------------------+-------------+------------------+----------+
  </code></pre>
  
  <p>This data for each car move  and its start X,Y, AND total Distance,</p>
  
  <p>any way better than CURSOR as i believe CURSOR is SLOW,  to make the following</p>
  
  <ul>
  <li>list all drives ,  and X,Y  from the start drive and x,y of the next drive stop
  grouped by Objectid</li>
  <li>list all Stops ,  and its X,Y  as Stop mean time between each 2 drive rows 
  grouped by Objectid  "for same object in reportgroup"</li>
  </ul>
  
  <p>i am asking about best way as this report can have more than 1 million tripstop row</p>
  
  <p>Thats the expected result  i have by CURSOR , it take long time to output</p>
  
  <pre><code>    +----------+-------------------------+-------------------------+------------+------------+------------+------------+----------+----------+----------+----------+----------+--------+-----------+----------+-----------+----------+---------+--------------+--------------+------------+------------+---------------------+------------+----------+-------------+----------+
  | 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 |
  +----------+-------------------------+-------------------------+------------+------------+------------+------------+----------+----------+----------+----------+----------+--------+-----------+----------+-----------+----------+---------+--------------+--------------+------------+------------+---------------------+------------+----------+-------------+----------+
  </code></pre>