Mike McCracken
asked on
Complex SQL
The SQL below provides data on vehicles in our fleet. It works correctly except when the vehicle changes. When that happens the previous odometer reading returns the odometer reading from the previous vehicle.
How do I ensure I get all the data and return 0 or NULL for the previous odometer reading in the first record of the vehicle
My thought is to use a CASE for the previous mileage
CASE WHEN [Current Row].meter < [Previous Row].meter THEN 0 ELSE [Previous Row].meter END ASpm,
Since vehicles are purchased new that will start the previous mileage correctly.
mlmcc
How do I ensure I get all the data and return 0 or NULL for the previous odometer reading in the first record of the vehicle
SELECT RN = ROW_NUMBER() OVER (ORDER BY vehicle_id), *
FROM fuel_transaction
SELECT [Current Row].vehicle_id, [Previous Row].meter pm, [Current Row].meter cm,
CASE WHEN [Current Row].meter - [Previous Row].meter < 0 THEN 0 ELSE [Current Row].meter - [Previous Row].meter END AS driven_miles,
[Current Row].cost_per_unit, [Current Row].total_units, [Current Row].cost_total,
CASE WHEN [Current Row].meter - [Previous Row].meter < 0 THEN 0 ELSE ([Current Row].meter - [Previous Row].meter) / [Current Row].total_units END AS MPG,
[Current Row].date_trans, [Current Row].time_trans
FROM CTE[Current Row] LEFT JOIN
CTE[Previous Row] ON [Previous Row].RN = [Current Row].RN - 1My thought is to use a CASE for the previous mileage
CASE WHEN [Current Row].meter < [Previous Row].meter THEN 0 ELSE [Previous Row].meter END ASpm,
SELECT RN = ROW_NUMBER() OVER (ORDER BY vehicle_id), *
FROM fuel_transaction
SELECT [Current Row].vehicle_id,
CASE WHEN [Current Row].meter < [Previous Row].meter THEN 0 ELSE [Previous Row].meter END AS pm,
[Current Row].meter cm,
CASE WHEN [Current Row].meter - [Previous Row].meter < 0 THEN 0 ELSE [Current Row].meter - [Previous Row].meter END AS driven_miles,
[Current Row].cost_per_unit, [Current Row].total_units, [Current Row].cost_total,
CASE WHEN [Current Row].meter - [Previous Row].meter < 0 THEN 0 ELSE ([Current Row].meter - [Previous Row].meter) / [Current Row].total_units END AS MPG,
[Current Row].date_trans, [Current Row].time_trans
FROM CTE[Current Row] LEFT JOIN
CTE[Previous Row] ON [Previous Row].RN = [Current Row].RN - 1Since vehicles are purchased new that will start the previous mileage correctly.
mlmcc
Pawan Kumar
could you please provide some sample data and the expected output.
ASKER
Here is a spreadsheet. There are 3 worksheets appropriately labeled
The first is the data from the table fuel_transaction
The second is from the view as shown. Problem cells are highlighted in YELLOW
The third shows the desired results also highlighted in YELLOW
mlmcc
Mileage.xlsx
The first is the data from the table fuel_transaction
The second is from the view as shown. Problem cells are highlighted in YELLOW
The third shows the desired results also highlighted in YELLOW
mlmcc
Mileage.xlsx
What version of SQL Server do you use?
Do you have the ability to use LEAD() & LAG() ?
Do you have the ability to use LEAD() & LAG() ?
ASKER
MS SQL Server 2012
I don't know about LEAD and LAG.
mlmcc
I don't know about LEAD and LAG.
mlmcc
please also see the code used below at http://rextester.com/XHIGA48889
LEAD() and LAG() allow you to get access to rows ahead or behind the current row.
e.g.
which produces the following output:
LEAD() and LAG() allow you to get access to rows ahead or behind the current row.
e.g.
select
vehicle_id
, meter
, SUM(meter) over(partition by vehicle_id order by date_trans, time_trans) as sum_meter
, lead(meter) over(partition by vehicle_id order by date_trans, time_trans) as lead_meter
, date_trans
, time_trans
from @fuel_transaction
order by
vehicle_id
, date_trans
, time_trans
;which produces the following output:
+-----+------------+-------+-----------+------------+---------------------+---------------------+
| | vehicle_id | meter | sum_meter | lead_meter | date_trans | time_trans |
+-----+------------+-------+-----------+------------+---------------------+---------------------+
| 1 | 1109 | 68797 | 68797 | 68984 | 06.01.2016 00:00:00 | 30.12.1899 02:41:00 |
| 2 | 1109 | 68984 | 137781 | 69209 | 13.01.2016 00:00:00 | 30.12.1899 11:43:00 |
| 3 | 1109 | 69209 | 206990 | 69382 | 26.01.2016 00:00:00 | 30.12.1899 12:01:00 |
| 4 | 1109 | 69382 | 276372 | 69525 | 29.01.2016 00:00:00 | 30.12.1899 12:33:00 |
| 5 | 1109 | 69525 | 345897 | 69691 | 04.02.2016 00:00:00 | 30.12.1899 16:13:00 |
| 6 | 1109 | 69691 | 415588 | 69843 | 12.02.2016 00:00:00 | 30.12.1899 12:25:00 |
| 7 | 1109 | 69843 | 485431 | 70232 | 23.02.2016 00:00:00 | 30.12.1899 07:41:00 |
| 8 | 1109 | 70232 | 555663 | 70428 | 01.03.2016 00:00:00 | 30.12.1899 19:15:00 |
| 9 | 1109 | 70428 | 626091 | 70685 | 07.03.2016 00:00:00 | 30.12.1899 09:51:00 |
| 10 | 1109 | 70685 | 696776 | 70840 | 23.03.2016 00:00:00 | 30.12.1899 10:03:00 |
| 11 | 1109 | 70840 | 767616 | 71253 | 29.03.2016 00:00:00 | 30.12.1899 06:28:00 |
| 12 | 1109 | 71253 | 838869 | 71469 | 03.04.2016 00:00:00 | 30.12.1899 18:28:00 |
| 13 | 1109 | 71469 | 910338 | 71756 | 12.04.2016 00:00:00 | 30.12.1899 15:47:00 |
| 14 | 1109 | 71756 | 982094 | 72007 | 17.04.2016 00:00:00 | 30.12.1899 15:38:00 |
| 15 | 1109 | 72007 | 1054101 | 72266 | 22.04.2016 00:00:00 | 30.12.1899 11:02:37 |
| 16 | 1109 | 72266 | 1126367 | 72348 | 29.04.2016 00:00:00 | 30.12.1899 11:09:30 |
| 17 | 1109 | 72348 | 1198715 | 72623 | 05.05.2016 00:00:00 | 30.12.1899 16:03:00 |
| 18 | 1109 | 72623 | 1271338 | 72748 | 17.05.2016 00:00:00 | 30.12.1899 05:26:00 |
| 19 | 1109 | 72748 | 1344086 | 73040 | 21.05.2016 00:00:00 | 30.12.1899 10:23:09 |
| 20 | 1109 | 73040 | 1417126 | 73201 | 01.06.2016 00:00:00 | 30.12.1899 16:38:00 |
| 21 | 1109 | 73201 | 1490327 | 73379 | 14.06.2016 00:00:00 | 30.12.1899 18:09:00 |
| 22 | 1109 | 73379 | 1563706 | 73716 | 24.06.2016 00:00:00 | 30.12.1899 07:49:46 |
| 23 | 1109 | 73716 | 1637422 | 73873 | 28.06.2016 00:00:00 | 30.12.1899 14:33:00 |
| 24 | 1109 | 73873 | 1711295 | 74180 | 11.07.2016 00:00:00 | 30.12.1899 12:01:00 |
| 25 | 1109 | 74180 | 1785475 | 74458 | 13.07.2016 00:00:00 | 30.12.1899 07:09:00 |
| 26 | 1109 | 74458 | 1859933 | 74650 | 18.07.2016 00:00:00 | 30.12.1899 13:07:00 |
| 27 | 1109 | 74650 | 1934583 | 74833 | 19.07.2016 00:00:00 | 30.12.1899 13:49:00 |
| 28 | 1109 | 74833 | 2009416 | 74834 | 26.07.2016 00:00:00 | 30.12.1899 06:25:00 |
| 29 | 1109 | 74834 | 2084250 | 75058 | 26.07.2016 00:00:00 | 30.12.1899 06:29:00 |
| 30 | 1109 | 75058 | 2159308 | 75260 | 29.07.2016 00:00:00 | 30.12.1899 22:04:00 |
| 31 | 1109 | 75260 | 2234568 | 75449 | 06.08.2016 00:00:00 | 30.12.1899 06:52:00 |
| 32 | 1109 | 75449 | 2310017 | 76609 | 08.08.2016 00:00:00 | 30.12.1899 13:43:00 |
| 33 | 1109 | 76609 | 2386626 | 75825 | 10.08.2016 00:00:00 | 30.12.1899 14:32:00 |
| 34 | 1109 | 75825 | 2462451 | 76097 | 19.08.2016 00:00:00 | 30.12.1899 11:00:00 |
| 35 | 1109 | 76097 | 2538548 | 76283 | 31.08.2016 00:00:00 | 30.12.1899 11:13:00 |
| 36 | 1109 | 76283 | 2614831 | 76539 | 10.09.2016 00:00:00 | 30.12.1899 21:37:00 |
| 37 | 1109 | 76539 | 2691370 | 76747 | 13.09.2016 00:00:00 | 30.12.1899 12:36:00 |
| 38 | 1109 | 76747 | 2768117 | 76894 | 19.09.2016 00:00:00 | 30.12.1899 13:16:00 |
| 39 | 1109 | 76894 | 2845011 | 77180 | 23.09.2016 00:00:00 | 30.12.1899 11:58:00 |
| 40 | 1109 | 77180 | 2922191 | 77473 | 07.10.2016 00:00:00 | 30.12.1899 14:21:00 |
| 41 | 1109 | 77473 | 2999664 | 77656 | 14.10.2016 00:00:00 | 30.12.1899 06:35:05 |
| 42 | 1109 | 77656 | 3077320 | 77890 | 24.10.2016 00:00:00 | 30.12.1899 10:13:00 |
| 43 | 1109 | 77890 | 3155210 | 78049 | 01.11.2016 00:00:00 | 30.12.1899 14:39:00 |
| 44 | 1109 | 78049 | 3233259 | NULL | 15.11.2016 00:00:00 | 30.12.1899 15:09:00 |
| 45 | 1110 | 10198 | 10198 | 10353 | 07.06.2012 00:00:00 | 30.12.1899 13:27:07 |
| 46 | 1110 | 10353 | 20551 | 10785 | 25.06.2012 00:00:00 | 30.12.1899 07:35:00 |
| 47 | 1110 | 10785 | 31336 | 11055 | 04.07.2012 00:00:00 | 30.12.1899 07:37:00 |
| 48 | 1110 | 11055 | 42391 | 11440 | 20.07.2012 00:00:00 | 30.12.1899 14:37:00 |
| 49 | 1110 | 11440 | 53831 | 11673 | 27.07.2012 00:00:00 | 30.12.1899 08:07:59 |
| 50 | 1110 | 11673 | 65504 | 11903 | 09.08.2012 00:00:00 | 30.12.1899 09:34:52 |
| 51 | 1110 | 11903 | 77407 | 13032 | 21.08.2012 00:00:00 | 30.12.1899 07:04:00 |
| 52 | 1110 | 13032 | 90439 | 13850 | 27.09.2012 00:00:00 | 30.12.1899 09:22:31 |
| 53 | 1110 | 13850 | 104289 | 14683 | 24.12.2012 00:00:00 | 30.12.1899 11:28:33 |
| 54 | 1110 | 14683 | 118972 | 14913 | 01.01.2013 00:00:00 | 30.12.1899 12:00:42 |
| 55 | 1110 | 14913 | 133885 | 15113 | 09.01.2013 00:00:00 | 30.12.1899 10:23:31 |
| 56 | 1110 | 15113 | 148998 | 15337 | 26.01.2013 00:00:00 | 30.12.1899 09:23:00 |
| 57 | 1110 | 15337 | 164335 | 15545 | 18.02.2013 00:00:00 | 30.12.1899 07:16:00 |
| 58 | 1110 | 15545 | 179880 | 15763 | 05.03.2013 00:00:00 | 30.12.1899 09:06:00 |
| 59 | 1110 | 15763 | 195643 | 16015 | 21.03.2013 00:00:00 | 30.12.1899 08:57:00 |
| 60 | 1110 | 16015 | 211658 | 16201 | 04.04.2013 00:00:00 | 30.12.1899 08:19:00 |
| 61 | 1110 | 16201 | 227859 | 16573 | 19.04.2013 00:00:00 | 30.12.1899 08:24:00 |
| 62 | 1110 | 16573 | 244432 | 16896 | 06.05.2013 00:00:00 | 30.12.1899 07:12:00 |
| 63 | 1110 | 16896 | 261328 | 17206 | 16.05.2013 00:00:00 | 30.12.1899 11:16:00 |
| 64 | 1110 | 17206 | 278534 | 17482 | 31.05.2013 00:00:00 | 30.12.1899 12:58:00 |
| 65 | 1110 | 17482 | 296016 | 17767 | 17.06.2013 00:00:00 | 30.12.1899 06:20:00 |
| 66 | 1110 | 17767 | 313783 | 18061 | 15.07.2013 00:00:00 | 30.12.1899 09:18:00 |
| 67 | 1110 | 18061 | 331844 | 18339 | 29.07.2013 00:00:00 | 30.12.1899 12:18:00 |
| 68 | 1110 | 18339 | 350183 | 18546 | 08.08.2013 00:00:00 | 30.12.1899 12:25:00 |
| 69 | 1110 | 18546 | 368729 | 18856 | 21.08.2013 00:00:00 | 30.12.1899 12:16:00 |
| 70 | 1110 | 18856 | 387585 | 19136 | 06.09.2013 00:00:00 | 30.12.1899 11:13:00 |
| 71 | 1110 | 19136 | 406721 | 19568 | 18.09.2013 00:00:00 | 30.12.1899 11:08:00 |
| 72 | 1110 | 19568 | 426289 | 19785 | 21.10.2013 00:00:00 | 30.12.1899 13:24:00 |
| 73 | 1110 | 19785 | 446074 | 20421 | 23.10.2013 00:00:00 | 30.12.1899 11:07:00 |
| 74 | 1110 | 20421 | 466495 | 20674 | 15.11.2013 00:00:00 | 30.12.1899 06:24:00 |
| 75 | 1110 | 20674 | 487169 | 20943 | 03.12.2013 00:00:00 | 30.12.1899 10:50:00 |
| 76 | 1110 | 20943 | 508112 | 21190 | 13.12.2013 00:00:00 | 30.12.1899 10:20:00 |
| 77 | 1110 | 21190 | 529302 | 21413 | 03.01.2014 00:00:00 | 30.12.1899 07:33:00 |
| 78 | 1110 | 21413 | 550715 | 21646 | 20.01.2014 00:00:00 | 30.12.1899 11:27:00 |
| 79 | 1110 | 21646 | 572361 | 21702 | 30.01.2014 00:00:00 | 30.12.1899 14:25:00 |
| 80 | 1110 | 21702 | 594063 | 21972 | 04.02.2014 00:00:00 | 30.12.1899 09:38:00 |
| 81 | 1110 | 21972 | 616035 | 22481 | 24.02.2014 00:00:00 | 30.12.1899 07:30:00 |
| 82 | 1110 | 22481 | 638516 | 23071 | 28.03.2014 00:00:00 | 30.12.1899 09:00:00 |
| 83 | 1110 | 23071 | 661587 | 23389 | 02.05.2014 00:00:00 | 30.12.1899 11:34:00 |
| 84 | 1110 | 23389 | 684976 | 23693 | 13.05.2014 00:00:00 | 30.12.1899 13:46:00 |
| 85 | 1110 | 23693 | 708669 | 23957 | 29.05.2014 00:00:00 | 30.12.1899 11:04:00 |
| 86 | 1110 | 23957 | 732626 | 24259 | 17.06.2014 00:00:00 | 30.12.1899 10:32:00 |
| 87 | 1110 | 24259 | 756885 | 24538 | 07.07.2014 00:00:00 | 30.12.1899 12:16:00 |
| 88 | 1110 | 24538 | 781423 | 25139 | 24.07.2014 00:00:00 | 30.12.1899 11:20:00 |
| 89 | 1110 | 25139 | 806562 | 25426 | 25.08.2014 00:00:00 | 30.12.1899 04:11:00 |
| 90 | 1110 | 25426 | 831988 | 25198 | 27.08.2014 00:00:00 | 30.12.1899 03:55:00 |
| 91 | 1110 | 25198 | 857186 | 25759 | 27.08.2014 00:00:00 | 30.12.1899 07:14:00 |
| 92 | 1110 | 25759 | 882945 | 26067 | 02.09.2014 00:00:00 | 30.12.1899 06:17:00 |
| 93 | 1110 | 26067 | 909012 | 26666 | 18.09.2014 00:00:00 | 30.12.1899 06:45:00 |
| 94 | 1110 | 26666 | 935678 | 26924 | 27.10.2014 00:00:00 | 30.12.1899 11:22:00 |
| 95 | 1110 | 26924 | 962602 | 27107 | 12.11.2014 00:00:00 | 30.12.1899 08:22:00 |
| 96 | 1110 | 27107 | 989709 | 27373 | 21.11.2014 00:00:00 | 30.12.1899 07:32:00 |
| 97 | 1110 | 27373 | 1017082 | 27609 | 12.12.2014 00:00:00 | 30.12.1899 06:32:00 |
| 98 | 1110 | 27609 | 1044691 | 27839 | 23.12.2014 00:00:00 | 30.12.1899 08:40:00 |
| 99 | 1110 | 27839 | 1072530 | 28061 | 19.01.2015 00:00:00 | 30.12.1899 07:18:00 |
| 100 | 1110 | 28061 | 1100591 | 28366 | 29.01.2015 00:00:00 | 30.12.1899 08:27:00 |
| 101 | 1110 | 28366 | 1128957 | 28544 | 16.02.2015 00:00:00 | 30.12.1899 09:53:00 |
| 102 | 1110 | 28544 | 1157501 | 28890 | 18.02.2015 00:00:00 | 30.12.1899 01:40:00 |
| 103 | 1110 | 28890 | 1186391 | 29169 | 25.02.2015 00:00:00 | 30.12.1899 14:03:00 |
| 104 | 1110 | 29169 | 1215560 | 29459 | 09.03.2015 00:00:00 | 30.12.1899 09:44:00 |
| 105 | 1110 | 29459 | 1245019 | 29775 | 26.03.2015 00:00:00 | 30.12.1899 07:52:00 |
| 106 | 1110 | 29775 | 1274794 | 30371 | 08.04.2015 00:00:00 | 30.12.1899 07:21:00 |
| 107 | 1110 | 30371 | 1305165 | 30987 | 30.04.2015 00:00:00 | 30.12.1899 06:35:00 |
| 108 | 1110 | 30987 | 1336152 | 31306 | 05.06.2015 00:00:00 | 30.12.1899 07:36:00 |
| 109 | 1110 | 31306 | 1367458 | 31603 | 16.06.2015 00:00:00 | 30.12.1899 08:40:00 |
| 110 | 1110 | 31603 | 1399061 | 31914 | 29.06.2015 00:00:00 | 30.12.1899 01:36:00 |
| 111 | 1110 | 31914 | 1430975 | 32235 | 10.07.2015 00:00:00 | 30.12.1899 08:16:00 |
| 112 | 1110 | 32235 | 1463210 | 32527 | 22.07.2015 00:00:00 | 30.12.1899 09:58:00 |
| 113 | 1110 | 32527 | 1495737 | 32832 | 30.07.2015 00:00:00 | 30.12.1899 10:11:00 |
| 114 | 1110 | 32832 | 1528569 | 33140 | 12.08.2015 00:00:00 | 30.12.1899 06:54:00 |
| 115 | 1110 | 33140 | 1561709 | 33452 | 25.08.2015 00:00:00 | 30.12.1899 10:16:00 |
| 116 | 1110 | 33452 | 1595161 | 33738 | 09.09.2015 00:00:00 | 30.12.1899 10:21:00 |
| 117 | 1110 | 33738 | 1628899 | 34053 | 21.09.2015 00:00:00 | 30.12.1899 07:06:00 |
| 118 | 1110 | 34053 | 1662952 | 34346 | 01.10.2015 00:00:00 | 30.12.1899 11:03:00 |
| 119 | 1110 | 34346 | 1697298 | 34563 | 14.10.2015 00:00:00 | 30.12.1899 07:39:00 |
| 120 | 1110 | 34563 | 1731861 | 34387 | 29.10.2015 00:00:00 | 30.12.1899 05:56:00 |
| 121 | 1110 | 34387 | 1766248 | 35158 | 09.11.2015 00:00:00 | 30.12.1899 14:39:52 |
| 122 | 1110 | 35158 | 1801406 | 35441 | 20.11.2015 00:00:00 | 30.12.1899 06:28:00 |
| 123 | 1110 | 35441 | 1836847 | 35742 | 02.12.2015 00:00:00 | 30.12.1899 12:35:00 |
| 124 | 1110 | 35742 | 1872589 | 35982 | 11.12.2015 00:00:00 | 30.12.1899 12:07:00 |
| 125 | 1110 | 35982 | 1908571 | 36075 | 23.12.2015 00:00:00 | 30.12.1899 06:43:00 |
| 126 | 1110 | 36075 | 1944646 | 36220 | 29.12.2015 00:00:00 | 30.12.1899 07:16:00 |
| 127 | 1110 | 36220 | 1980866 | 36406 | 06.01.2016 00:00:00 | 30.12.1899 07:34:00 |
| 128 | 1110 | 36406 | 2017272 | 36537 | 13.01.2016 00:00:00 | 30.12.1899 06:56:00 |
| 129 | 1110 | 36537 | 2053809 | 36826 | 19.01.2016 00:00:00 | 30.12.1899 10:12:00 |
| 130 | 1110 | 36826 | 2090635 | 37109 | 29.01.2016 00:00:00 | 30.12.1899 06:35:00 |
| 131 | 1110 | 37109 | 2127744 | 37709 | 11.02.2016 00:00:00 | 30.12.1899 13:13:00 |
| 132 | 1110 | 37709 | 2165453 | 38018 | 07.03.2016 00:00:00 | 30.12.1899 13:31:00 |
| 133 | 1110 | 38018 | 2203471 | 38314 | 18.03.2016 00:00:00 | 30.12.1899 13:27:00 |
| 134 | 1110 | 38314 | 2241785 | 38622 | 01.04.2016 00:00:00 | 30.12.1899 07:45:00 |
| 135 | 1110 | 38622 | 2280407 | 38888 | 11.04.2016 00:00:00 | 30.12.1899 15:10:00 |
| 136 | 1110 | 38888 | 2319295 | 39220 | 22.04.2016 00:00:00 | 30.12.1899 06:47:00 |
| 137 | 1110 | 39220 | 2358515 | 39524 | 03.05.2016 00:00:00 | 30.12.1899 14:35:00 |
| 138 | 1110 | 39524 | 2398039 | 39805 | 12.05.2016 00:00:00 | 30.12.1899 09:44:52 |
| 139 | 1110 | 39805 | 2437844 | 40127 | 23.05.2016 00:00:00 | 30.12.1899 07:08:00 |
| 140 | 1110 | 40127 | 2477971 | 40428 | 06.06.2016 00:00:00 | 30.12.1899 14:47:00 |
| 141 | 1110 | 40428 | 2518399 | 40745 | 17.06.2016 00:00:00 | 30.12.1899 07:08:00 |
| 142 | 1110 | 40745 | 2559144 | 41003 | 27.06.2016 00:00:00 | 30.12.1899 16:39:00 |
| 143 | 1110 | 41003 | 2600147 | 41308 | 11.07.2016 00:00:00 | 30.12.1899 07:12:00 |
| 144 | 1110 | 41308 | 2641455 | 41620 | 20.07.2016 00:00:00 | 30.12.1899 12:17:00 |
| 145 | 1110 | 41620 | 2683075 | 41897 | 02.08.2016 00:00:00 | 30.12.1899 06:04:00 |
| 146 | 1110 | 41897 | 2724972 | 42202 | 15.08.2016 00:00:00 | 30.12.1899 07:19:00 |
| 147 | 1110 | 42202 | 2767174 | 42527 | 29.08.2016 00:00:00 | 30.12.1899 13:30:00 |
| 148 | 1110 | 42527 | 2809701 | 42829 | 12.09.2016 00:00:00 | 30.12.1899 10:12:00 |
| 149 | 1110 | 42829 | 2852530 | 43141 | 21.09.2016 00:00:00 | 30.12.1899 09:21:00 |
| 150 | 1110 | 43141 | 2895671 | NULL | 28.09.2016 00:00:00 | 30.12.1899 12:45:00 |
| 151 | 1112 | 21000 | 21000 | 21419 | 11.06.2012 00:00:00 | 30.12.1899 07:00:00 |
| 152 | 1112 | 21419 | 42419 | 21771 | 28.06.2012 00:00:00 | 30.12.1899 07:16:03 |
| 153 | 1112 | 21771 | 64190 | 22093 | 05.07.2012 00:00:00 | 30.12.1899 10:00:00 |
| 154 | 1112 | 22093 | 86283 | 22438 | 11.07.2012 00:00:00 | 30.12.1899 06:00:00 |
| 155 | 1112 | 22438 | 108721 | 22825 | 17.07.2012 00:00:00 | 30.12.1899 07:00:00 |
| 156 | 1112 | 22825 | 131546 | 23200 | 20.07.2012 00:00:00 | 30.12.1899 14:57:00 |
| 157 | 1112 | 23200 | 154746 | 23570 | 26.07.2012 00:00:00 | 30.12.1899 05:44:00 |
| 158 | 1112 | 23570 | 178316 | 23928 | 02.08.2012 00:00:00 | 30.12.1899 07:00:00 |
| 159 | 1112 | 23928 | 202244 | 24285 | 08.08.2012 00:00:00 | 30.12.1899 01:00:00 |
| 160 | 1112 | 24285 | 226529 | 24654 | 16.08.2012 00:00:00 | 30.12.1899 07:50:00 |
| 161 | 1112 | 24654 | 251183 | 25035 | 24.08.2012 00:00:00 | 30.12.1899 05:47:00 |
| 162 | 1112 | 25035 | 276218 | 25410 | 04.09.2012 00:00:00 | 30.12.1899 07:30:58 |
| 163 | 1112 | 25410 | 301628 | 25837 | 12.09.2012 00:00:00 | 30.12.1899 07:10:00 |
| 164 | 1112 | 25837 | 327465 | 26200 | 19.09.2012 00:00:00 | 30.12.1899 07:37:06 |
| 165 | 1112 | 26200 | 353665 | 26510 | 27.09.2012 00:00:00 | 30.12.1899 07:51:00 |
| 166 | 1112 | 26510 | 380175 | 26853 | 04.10.2012 00:00:00 | 30.12.1899 07:45:00 |
| 167 | 1112 | 26853 | 407028 | 27230 | 12.10.2012 00:00:00 | 30.12.1899 07:39:00 |
| 168 | 1112 | 27230 | 434258 | 27640 | 18.10.2012 00:00:00 | 30.12.1899 07:30:00 |
| 169 | 1112 | 27640 | 461898 | 28035 | 23.10.2012 00:00:00 | 30.12.1899 14:45:19 |
| 170 | 1112 | 28035 | 489933 | 28396 | 29.10.2012 00:00:00 | 30.12.1899 07:00:00 |
| 171 | 1112 | 28396 | 518329 | 28795 | 01.11.2012 00:00:00 | 30.12.1899 02:30:00 |
| 172 | 1112 | 28795 | 547124 | 29138 | 06.11.2012 00:00:00 | 30.12.1899 14:15:00 |
| 173 | 1112 | 29138 | 576262 | 29550 | 09.11.2012 00:00:00 | 30.12.1899 07:40:55 |
| 174 | 1112 | 29550 | 605812 | 29950 | 15.11.2012 00:00:00 | 30.12.1899 14:05:32 |
| 175 | 1112 | 29950 | 635762 | 30315 | 27.11.2012 00:00:00 | 30.12.1899 07:12:09 |
| 176 | 1112 | 30315 | 666077 | 30650 | 05.12.2012 00:00:00 | 30.12.1899 14:52:53 |
| 177 | 1112 | 30650 | 696727 | 31008 | 14.12.2012 00:00:00 | 30.12.1899 08:00:00 |
| 178 | 1112 | 31008 | 727735 | 31250 | 19.12.2012 00:00:00 | 30.12.1899 08:27:46 |
| 179 | 1112 | 31250 | 758985 | 31566 | 31.12.2012 00:00:00 | 30.12.1899 07:00:00 |
| 180 | 1112 | 31566 | 790551 | 31900 | 08.01.2013 00:00:00 | 30.12.1899 13:45:57 |
| 181 | 1112 | 31900 | 822451 | 32188 | 15.01.2013 00:00:00 | 30.12.1899 13:50:07 |
| 182 | 1112 | 32188 | 854639 | 32496 | 22.01.2013 00:00:00 | 30.12.1899 13:39:29 |
| 183 | 1112 | 32496 | 887135 | 32854 | 28.01.2013 00:00:00 | 30.12.1899 09:00:00 |
| 184 | 1112 | 32854 | 919989 | 33143 | 01.02.2013 00:00:00 | 30.12.1899 14:32:04 |
| 185 | 1112 | 33143 | 953132 | 33490 | 08.02.2013 00:00:00 | 30.12.1899 06:00:00 |
| 186 | 1112 | 33490 | 986622 | 33849 | 15.02.2013 00:00:00 | 30.12.1899 14:24:00 |
| 187 | 1112 | 33849 | 1020471 | 34162 | 22.02.2013 00:00:00 | 30.12.1899 12:00:00 |
| 188 | 1112 | 34162 | 1054633 | 34475 | 01.03.2013 00:00:00 | 30.12.1899 07:00:00 |
| 189 | 1112 | 34475 | 1089108 | 34840 | 07.03.2013 00:00:00 | 30.12.1899 07:40:00 |
| 190 | 1112 | 34840 | 1123948 | 35160 | 14.03.2013 00:00:00 | 30.12.1899 08:00:00 |
| 191 | 1112 | 35160 | 1159108 | 35506 | 20.03.2013 00:00:00 | 30.12.1899 13:00:00 |
| 192 | 1112 | 35506 | 1194614 | 35800 | 27.03.2013 00:00:00 | 30.12.1899 13:59:05 |
| 193 | 1112 | 35800 | 1230414 | 36100 | 04.04.2013 00:00:00 | 30.12.1899 07:30:00 |
| 194 | 1112 | 36100 | 1266514 | 36432 | 09.04.2013 00:00:00 | 30.12.1899 14:00:00 |
| 195 | 1112 | 36432 | 1302946 | 36803 | 16.04.2013 00:00:00 | 30.12.1899 14:57:42 |
| 196 | 1112 | 36803 | 1339749 | 37171 | 22.04.2013 00:00:00 | 30.12.1899 08:00:00 |
| 197 | 1112 | 37171 | 1376920 | 37568 | 29.04.2013 00:00:00 | 30.12.1899 07:00:00 |
| 198 | 1112 | 37568 | 1414488 | 37908 | 06.05.2013 00:00:00 | 30.12.1899 09:00:00 |
| 199 | 1112 | 37908 | 1452396 | 38295 | 13.05.2013 00:00:00 | 30.12.1899 08:00:00 |
| 200 | 1112 | 38295 | 1490691 | 38660 | 17.05.2013 00:00:00 | 30.12.1899 06:20:00 |
| 201 | 1112 | 38660 | 1529351 | 39000 | 24.05.2013 00:00:00 | 30.12.1899 07:00:00 |
| 202 | 1112 | 39000 | 1568351 | 39342 | 11.06.2013 00:00:00 | 30.12.1899 07:15:00 |
| 203 | 1112 | 39342 | 1607693 | 39695 | 17.06.2013 00:00:00 | 30.12.1899 13:00:00 |
| 204 | 1112 | 39695 | 1647388 | 40058 | 24.06.2013 00:00:00 | 30.12.1899 07:15:00 |
| 205 | 1112 | 40058 | 1687446 | 40430 | 28.06.2013 00:00:00 | 30.12.1899 13:49:07 |
| 206 | 1112 | 40430 | 1727876 | 40795 | 09.07.2013 00:00:00 | 30.12.1899 14:41:37 |
| 207 | 1112 | 40795 | 1768671 | 41155 | 16.07.2013 00:00:00 | 30.12.1899 13:31:54 |
| 208 | 1112 | 41155 | 1809826 | 41536 | 23.07.2013 00:00:00 | 30.12.1899 14:14:37 |
| 209 | 1112 | 41536 | 1851362 | 41889 | 31.07.2013 00:00:00 | 30.12.1899 13:27:51 |
| 210 | 1112 | 41889 | 1893251 | 42304 | 09.08.2013 00:00:00 | 30.12.1899 08:00:00 |
| 211 | 1112 | 42304 | 1935555 | 42633 | 16.08.2013 00:00:00 | 30.12.1899 10:30:00 |
| 212 | 1112 | 42633 | 1978188 | 42963 | 26.08.2013 00:00:00 | 30.12.1899 09:30:00 |
| 213 | 1112 | 42963 | 2021151 | 43325 | 30.08.2013 00:00:00 | 30.12.1899 08:30:00 |
| 214 | 1112 | 43325 | 2064476 | 43683 | 10.09.2013 00:00:00 | 30.12.1899 07:20:00 |
| 215 | 1112 | 43683 | 2108159 | 44040 | 16.09.2013 00:00:00 | 30.12.1899 06:00:00 |
| 216 | 1112 | 44040 | 2152199 | NULL | 23.09.2013 00:00:00 | 30.12.1899 08:45:00 |
+-----+------------+-------+-----------+------------+---------------------+---------------------+declare @fuel_transaction table
([meter] int, [payment_type] varchar(4), [cost_per_unit] numeric, [total_units] numeric, [pump_id] varchar(4), [ac_id] varchar(8), [fuel_type_id] varchar(3), [units] varchar(4), [cost_total] int, [comments] varchar(62), [vehicle_id] int, [date_trans] datetime, [time_trans] datetime)
;
INSERT INTO @fuel_transaction
([meter], [payment_type], [cost_per_unit], [total_units], [pump_id], [ac_id], [fuel_type_id], [units], [cost_total], [comments], [vehicle_id], [date_trans], [time_trans])
VALUES
(68797, NULL, 1.92, 34.43, NULL, '53010108', 'GAS', NULL, 34.43, NULL, 1109, '2016-01-06 00:00:00', '1899-12-30 02:41:00'),
(68984, NULL, 1.73, 17.3, NULL, '53010108', 'GAS', NULL, 29.91, NULL, 1109, '2016-01-13 00:00:00', '1899-12-30 11:43:00'),
(69209, NULL, 1.68, 22, NULL, '53010108', 'GAS', NULL, 36.94, NULL, 1109, '2016-01-26 00:00:00', '1899-12-30 12:01:00'),
(69382, NULL, 1.66, 13.26, NULL, '53010108', 'GAS', NULL, 22, NULL, 1109, '2016-01-29 00:00:00', '1899-12-30 12:33:00'),
(69525, NULL, 1.6, 14.42, NULL, '53010108', 'GAS', NULL, 23.06, NULL, 1109, '2016-02-04 00:00:00', '1899-12-30 16:13:00'),
(69691, NULL, 1.5, 16.15, NULL, '53010108', 'GAS', NULL, 24.21, NULL, 1109, '2016-02-12 00:00:00', '1899-12-30 12:25:00'),
(69843, NULL, 1.5, 14.962, NULL, '53010108', 'GAS', NULL, 22.43, NULL, 1109, '2016-02-23 00:00:00', '1899-12-30 07:41:00'),
(70232, NULL, 1.76, 12.35, NULL, '53010108', 'GAS', NULL, 21.72, NULL, 1109, '2016-03-01 00:00:00', '1899-12-30 19:15:00'),
(70428, NULL, 1.76, 17.29, NULL, '53010108', 'GAS', NULL, 30.41, NULL, 1109, '2016-03-07 00:00:00', '1899-12-30 09:51:00'),
(70685, NULL, 1.97, 22.56, NULL, '53010108', 'GAS', NULL, 44.41, NULL, 1109, '2016-03-23 00:00:00', '1899-12-30 10:03:00'),
(70840, NULL, 1.97, 13.56, NULL, '53010108', 'GAS', NULL, 26.71, NULL, 1109, '2016-03-29 00:00:00', '1899-12-30 06:28:00'),
(71253, NULL, 1.9, 19.67, NULL, '53010108', 'GAS', NULL, 37.35, NULL, 1109, '2016-04-03 00:00:00', '1899-12-30 18:28:00'),
(71469, NULL, 1.95, 18.24, NULL, '53010108', 'GAS', NULL, 35.55, NULL, 1109, '2016-04-12 00:00:00', '1899-12-30 15:47:00'),
(71756, NULL, 2.17, 25.09, NULL, '53010108', 'GAS', NULL, 54.43, NULL, 1109, '2016-04-17 00:00:00', '1899-12-30 15:38:00'),
(72007, NULL, 2.12, 17.03, NULL, '53010108', 'GAS', NULL, 36.1, NULL, 1109, '2016-04-22 00:00:00', '1899-12-30 11:02:37'),
(72266, NULL, 2.21, 18.03, NULL, '53010108', 'GAS', NULL, 39.84, NULL, 1109, '2016-04-29 00:00:00', '1899-12-30 11:09:30'),
(72348, NULL, 1.97, 27.19, NULL, '53010108', 'GAS', NULL, 53.54, NULL, 1109, '2016-05-05 00:00:00', '1899-12-30 16:03:00'),
(72623, NULL, 2.13, 24.5, NULL, '53010108', 'GAS', NULL, 52.15, NULL, 1109, '2016-05-17 00:00:00', '1899-12-30 05:26:00'),
(72748, NULL, 2.13, 9.08, NULL, '53010108', 'GAS', NULL, 19.34, NULL, 1109, '2016-05-21 00:00:00', '1899-12-30 10:23:09'),
(73040, NULL, 2.2, 24.28, NULL, '53010108', 'GAS', NULL, 53.38, NULL, 1109, '2016-06-01 00:00:00', '1899-12-30 16:38:00'),
(73201, NULL, 2.18, 14.88, NULL, '53010108', 'GAS', NULL, 32.41, NULL, 1109, '2016-06-14 00:00:00', '1899-12-30 18:09:00'),
(73379, NULL, 2.34, 17.17, NULL, '53010108', 'GAS', NULL, 40.15, NULL, 1109, '2016-06-24 00:00:00', '1899-12-30 07:49:46'),
(73716, NULL, 2.15, 28.08, NULL, '53010108', 'GAS', NULL, 60.34, NULL, 1109, '2016-06-28 00:00:00', '1899-12-30 14:33:00'),
(73873, NULL, 2.08, 14.33, NULL, '53010108', 'GAS', NULL, 29.79, NULL, 1109, '2016-07-11 00:00:00', '1899-12-30 12:01:00'),
(74180, NULL, 2.1, 25.42, NULL, '53010108', 'GAS', NULL, 53.38, NULL, 1109, '2016-07-13 00:00:00', '1899-12-30 07:09:00'),
(74458, NULL, 2.06, 23.29, NULL, '53010108', 'GAS', NULL, 47.96, NULL, 1109, '2016-07-18 00:00:00', '1899-12-30 13:07:00'),
(74650, NULL, 2.08, 15.9, NULL, '53010108', 'GAS', NULL, 33.07, NULL, 1109, '2016-07-19 00:00:00', '1899-12-30 13:49:00'),
(74833, NULL, 2, 5.02, NULL, '53010108', 'GAS', NULL, 10.03, NULL, 1109, '2016-07-26 00:00:00', '1899-12-30 06:25:00'),
(74834, NULL, 2, 12.3, NULL, '53010108', 'GAS', NULL, 24.59, NULL, 1109, '2016-07-26 00:00:00', '1899-12-30 06:29:00'),
(75058, NULL, 2, 24.18, NULL, '53010108', 'GAS', NULL, 48.36, NULL, 1109, '2016-07-29 00:00:00', '1899-12-30 22:04:00'),
(75260, NULL, 2, 20.25, NULL, '53010108', 'GAS', NULL, 48.49, NULL, 1109, '2016-08-06 00:00:00', '1899-12-30 06:52:00'),
(75449, NULL, 2.13, 13.09, NULL, '53010108', 'GAS', NULL, 27.88, NULL, 1109, '2016-08-08 00:00:00', '1899-12-30 13:43:00'),
(76609, NULL, 2.1, 13.27, NULL, '53010108', 'GAS', NULL, 27.85, NULL, 1109, '2016-08-10 00:00:00', '1899-12-30 14:32:00'),
(75825, NULL, 2.33, 20.92, NULL, '53010108', 'GAS', NULL, 48.72, NULL, 1109, '2016-08-19 00:00:00', '1899-12-30 11:00:00'),
(76097, NULL, 2.15, 22.8, NULL, '53010108', 'GAS', NULL, 49.01, NULL, 1109, '2016-08-31 00:00:00', '1899-12-30 11:13:00'),
(76283, NULL, 1.98, 16.34, NULL, '53010108', 'GAS', NULL, 32.34, NULL, 1109, '2016-09-10 00:00:00', '1899-12-30 21:37:00'),
(76539, NULL, 2.18, 21.55, NULL, '53010108', 'GAS', NULL, 46.97, NULL, 1109, '2016-09-13 00:00:00', '1899-12-30 12:36:00'),
(76747, NULL, 1.96, 16.97, NULL, '53010108', 'GAS', NULL, 33.25, NULL, 1109, '2016-09-19 00:00:00', '1899-12-30 13:16:00'),
(76894, NULL, 2.06, 12.7, NULL, '53010108', 'GAS', NULL, 26.16, NULL, 1109, '2016-09-23 00:00:00', '1899-12-30 11:58:00'),
(77180, NULL, 2.42, 25.53, NULL, '53010108', 'GAS', NULL, 61.76, NULL, 1109, '2016-10-07 00:00:00', '1899-12-30 14:21:00'),
(77473, NULL, 2.1, 22.3, NULL, '53010108', 'GAS', NULL, 46.82, NULL, 1109, '2016-10-14 00:00:00', '1899-12-30 06:35:05'),
(77656, NULL, 2.12, 16, NULL, '53010108', 'GAS', NULL, 33.88, NULL, 1109, '2016-10-24 00:00:00', '1899-12-30 10:13:00'),
(77890, NULL, 2.1, 19.94, NULL, '53010108', 'GAS', NULL, 41.85, NULL, 1109, '2016-11-01 00:00:00', '1899-12-30 14:39:00'),
(78049, NULL, 1.98, 14.6, NULL, '53010108', 'GAS', NULL, 28.93, NULL, 1109, '2016-11-15 00:00:00', '1899-12-30 15:09:00'),
(10198, NULL, 3.29, 21.55, NULL, '53010110', 'GAS', NULL, 71.12, NULL, 1110, '2012-06-07 00:00:00', '1899-12-30 13:27:07'),
(10353, NULL, 3.57, 21.661, NULL, '53010108', 'GAS', NULL, 77.52, NULL, 1110, '2012-06-25 00:00:00', '1899-12-30 07:35:00'),
(10785, NULL, 3.48, 24.6, NULL, '53010108', 'GAS', NULL, 85.83, NULL, 1110, '2012-07-04 00:00:00', '1899-12-30 07:37:00'),
(11055, NULL, 3.39, 22.83, NULL, '53010108', 'GAS', NULL, 77.6, NULL, 1110, '2012-07-20 00:00:00', '1899-12-30 14:37:00'),
(11440, NULL, 3.42, 25.64, NULL, '53010108', 'GAS', NULL, 87.92, NULL, 1110, '2012-07-27 00:00:00', '1899-12-30 08:07:59'),
(11673, NULL, 3.65, 23.13, NULL, '53010108', 'GAS', NULL, 84.66, NULL, 1110, '2012-08-09 00:00:00', '1899-12-30 09:34:52'),
(11903, NULL, 3.65, 22.9, NULL, '53010108', 'GAS', NULL, 83.86, NULL, 1110, '2012-08-21 00:00:00', '1899-12-30 07:04:00'),
(13032, NULL, 3.72, 24.42, NULL, '53010108', 'GAS', NULL, 91.07, 'omaha trip', 1110, '2012-09-27 00:00:00', '1899-12-30 09:22:31'),
(13850, NULL, 2.99, 23.5, NULL, '53010108', 'GAS', NULL, 70.5, 'Dummy entry to true up the data', 1110, '2012-12-24 00:00:00', '1899-12-30 11:28:33'),
(14683, NULL, 3.46, 21.57, NULL, '53010108', 'GAS', NULL, 75.03, 'Dummy entry to true up the data', 1110, '2013-01-01 00:00:00', '1899-12-30 12:00:42'),
(14913, NULL, 2.99, 23.5, NULL, '53010108', 'GAS', NULL, 70.5, NULL, 1110, '2013-01-09 00:00:00', '1899-12-30 10:23:31'),
(15113, NULL, 3.16, 16.25, NULL, '53010108', 'GAS', NULL, 51.52, NULL, 1110, '2013-01-26 00:00:00', '1899-12-30 09:23:00'),
(15337, NULL, 3.77, 19.06, NULL, '53010108', 'GAS', NULL, 72.04, NULL, 1110, '2013-02-18 00:00:00', '1899-12-30 07:16:00'),
(15545, NULL, 3.67, 21.31, NULL, '53010108', 'GAS', NULL, 78.43, NULL, 1110, '2013-03-05 00:00:00', '1899-12-30 09:06:00'),
(15763, NULL, 3.47, 20.52, NULL, '53010108', 'GAS', NULL, 71.42, NULL, 1110, '2013-03-21 00:00:00', '1899-12-30 08:57:00'),
(16015, NULL, 3.47, 22.16, NULL, '53010108', 'GAS', NULL, 77.1, NULL, 1110, '2013-04-04 00:00:00', '1899-12-30 08:19:00'),
(16201, NULL, 3.27, 22.41, NULL, '53010108', 'GAS', NULL, 73.5, NULL, 1110, '2013-04-19 00:00:00', '1899-12-30 08:24:00'),
(16573, NULL, 3.47, 23.63, NULL, '53010108', 'GAS', NULL, 82.21, NULL, 1110, '2013-05-06 00:00:00', '1899-12-30 07:12:00'),
(16896, NULL, 3.89, 25.28, NULL, '53010108', 'GAS', NULL, 98.59, NULL, 1110, '2013-05-16 00:00:00', '1899-12-30 11:16:00'),
(17206, NULL, 3.69, 23.98, NULL, '53010108', 'GAS', NULL, 88.71, NULL, 1110, '2013-05-31 00:00:00', '1899-12-30 12:58:00'),
(17482, NULL, 3.54, 21.66, NULL, '53010108', 'GAS', NULL, 76.9, NULL, 1110, '2013-06-17 00:00:00', '1899-12-30 06:20:00'),
(17767, NULL, 3.56, 22.8, NULL, '53010108', 'GAS', NULL, 81.46, NULL, 1110, '2013-07-15 00:00:00', '1899-12-30 09:18:00'),
(18061, NULL, 3.65, 24.89, NULL, '53010108', 'GAS', NULL, 91.08, NULL, 1110, '2013-07-29 00:00:00', '1899-12-30 12:18:00'),
(18339, NULL, 3.59, 23.24, NULL, '53010108', 'GAS', NULL, 83.67, NULL, 1110, '2013-08-08 00:00:00', '1899-12-30 12:25:00'),
(18546, NULL, 3.57, 16.59, NULL, '53010108', 'GAS', NULL, 59.39, NULL, 1110, '2013-08-21 00:00:00', '1899-12-30 12:16:00'),
(18856, NULL, 3.62, 24.65, NULL, '53010108', 'GAS', NULL, 89.48, NULL, 1110, '2013-09-06 00:00:00', '1899-12-30 11:13:00'),
(19136, NULL, 3.52, 23.2, NULL, '53010108', 'GAS', NULL, 81.98, NULL, 1110, '2013-09-18 00:00:00', '1899-12-30 11:08:00'),
(19568, NULL, 3.05, 8.6, NULL, '53010108', 'GAS', NULL, 26.33, NULL, 1110, '2013-10-21 00:00:00', '1899-12-30 13:24:00'),
(19785, NULL, 3.22, 19.7, NULL, '53010108', 'GAS', NULL, 63.87, 'trip to Omaha for Ariba Training', 1110, '2013-10-23 00:00:00', '1899-12-30 11:07:00'),
(20421, NULL, 2.97, 25.81, NULL, '53010108', 'GAS', NULL, 76.89, NULL, 1110, '2013-11-15 00:00:00', '1899-12-30 06:24:00'),
(20674, NULL, 2.95, 22.68, NULL, '53010108', 'GAS', NULL, 67.12, NULL, 1110, '2013-12-03 00:00:00', '1899-12-30 10:50:00'),
(20943, NULL, 2.92, 24.36, NULL, '53010108', 'GAS', NULL, 71.35, NULL, 1110, '2013-12-13 00:00:00', '1899-12-30 10:20:00'),
(21190, NULL, 3.07, 22.2, NULL, '53010108', 'GAS', NULL, 68.44, NULL, 1110, '2014-01-03 00:00:00', '1899-12-30 07:33:00'),
(21413, NULL, 3.13, 22.65, NULL, '53010108', 'GAS', NULL, 71.11, NULL, 1110, '2014-01-20 00:00:00', '1899-12-30 11:27:00'),
(21646, NULL, 3.09, 21.7, NULL, '53010108', 'GAS', NULL, 67.46, NULL, 1110, '2014-01-30 00:00:00', '1899-12-30 14:25:00'),
(21702, NULL, 3.09, 10.38, NULL, '53010108', 'GAS', NULL, 32.18, NULL, 1110, '2014-02-04 00:00:00', '1899-12-30 09:38:00'),
(21972, NULL, 3.36, 24, NULL, '53010108', 'GAS', NULL, 80.95, NULL, 1110, '2014-02-24 00:00:00', '1899-12-30 07:30:00'),
(22481, NULL, 3.47, 22.6, NULL, '53010108', 'GAS', NULL, 78.69, NULL, 1110, '2014-03-28 00:00:00', '1899-12-30 09:00:00'),
(23071, NULL, 3.52, 22.6, NULL, '53010108', 'GAS', NULL, 79.78, NULL, 1110, '2014-05-02 00:00:00', '1899-12-30 11:34:00'),
(23389, NULL, 3.44, 24.4, NULL, '53010108', 'GAS', NULL, 84.17, NULL, 1110, '2014-05-13 00:00:00', '1899-12-30 13:46:00'),
(23693, NULL, 3.62, 24.85, NULL, '53010108', 'GAS', NULL, 90.2, NULL, 1110, '2014-05-29 00:00:00', '1899-12-30 11:04:00'),
(23957, NULL, 3.68, 21.5, NULL, '53010108', 'GAS', NULL, 79.22, NULL, 1110, '2014-06-17 00:00:00', '1899-12-30 10:32:00'),
(24259, NULL, 3.62, 24.8, NULL, '53010108', 'GAS', NULL, 90.06, NULL, 1110, '2014-07-07 00:00:00', '1899-12-30 12:16:00'),
(24538, NULL, 3.44, 23, NULL, '53010108', 'GAS', NULL, 79.5, NULL, 1110, '2014-07-24 00:00:00', '1899-12-30 11:20:00'),
(25139, NULL, 3.32, 25.4, NULL, '53010108', 'GAS', NULL, 84.7, NULL, 1110, '2014-08-25 00:00:00', '1899-12-30 04:11:00'),
(25426, NULL, 3.29, 15.7, NULL, '53010108', 'GAS', NULL, 51.83, 'omaha trip', 1110, '2014-08-27 00:00:00', '1899-12-30 03:55:00'),
(25198, NULL, 3.32, 5.94, NULL, '53010108', 'GAS', NULL, 19.78, 'top off for Omaha trip ...training', 1110, '2014-08-27 00:00:00', '1899-12-30 07:14:00'),
(25759, NULL, 3.29, 25.8, NULL, '53010108', 'GAS', NULL, 85.14, NULL, 1110, '2014-09-02 00:00:00', '1899-12-30 06:17:00'),
(26067, NULL, 3.26, 25.7, NULL, '53010108', 'GAS', NULL, 84.24, NULL, 1110, '2014-09-18 00:00:00', '1899-12-30 06:45:00'),
(26666, NULL, 2.94, 23.7, NULL, '53010108', 'GAS', NULL, 70, NULL, 1110, '2014-10-27 00:00:00', '1899-12-30 11:22:00'),
(26924, NULL, 2.81, 22.7, NULL, '53010108', 'GAS', NULL, 64.12, NULL, 1110, '2014-11-12 00:00:00', '1899-12-30 08:22:00'),
(27107, NULL, 2.69, 17.9, NULL, '53010108', 'GAS', NULL, 48.5, NULL, 1110, '2014-11-21 00:00:00', '1899-12-30 07:32:00'),
(27373, NULL, 2.35, 25.36, NULL, '53010108', 'GAS', NULL, 59.82, NULL, 1110, '2014-12-12 00:00:00', '1899-12-30 06:32:00'),
(27609, NULL, 1.99, 20.4, NULL, '53010108', 'GAS', NULL, 40.88, NULL, 1110, '2014-12-23 00:00:00', '1899-12-30 08:40:00'),
(27839, NULL, 1.75, 23.8, NULL, '53010108', 'GAS', NULL, 41.94, NULL, 1110, '2015-01-19 00:00:00', '1899-12-30 07:18:00'),
(28061, NULL, 1.96, 19.4, NULL, '53010108', 'GAS', NULL, 38.39, NULL, 1110, '2015-01-29 00:00:00', '1899-12-30 08:27:00'),
(28366, NULL, 2.16, 4.84, NULL, '53010108', 'GAS', NULL, 10.5, NULL, 1110, '2015-02-16 00:00:00', '1899-12-30 09:53:00'),
(28544, NULL, 2.34, 16.69, NULL, '53010108', 'GAS', NULL, 39.22, NULL, 1110, '2015-02-18 00:00:00', '1899-12-30 01:40:00'),
(28890, NULL, 2.24, 24.83, NULL, '53010108', 'GAS', NULL, 55.85, NULL, 1110, '2015-02-25 00:00:00', '1899-12-30 14:03:00'),
(29169, NULL, 2.34, 24.1, NULL, '53010108', 'GAS', NULL, 56.69, NULL, 1110, '2015-03-09 00:00:00', '1899-12-30 09:44:00'),
(29459, NULL, 2.24, 23.56, NULL, '53010108', 'GAS', NULL, 53, NULL, 1110, '2015-03-26 00:00:00', '1899-12-30 07:52:00'),
(29775, NULL, 2.26, 25.1, NULL, '53010108', 'GAS', NULL, 57, NULL, 1110, '2015-04-08 00:00:00', '1899-12-30 07:21:00'),
(30371, NULL, 2.34, 23.29, NULL, '53010108', 'GAS', NULL, 54.73, NULL, 1110, '2015-04-30 00:00:00', '1899-12-30 06:35:00'),
(30987, NULL, 2.65, 25.12, NULL, '53010108', 'GAS', NULL, 66.8, NULL, 1110, '2015-06-05 00:00:00', '1899-12-30 07:36:00'),
(31306, NULL, 2.75, 25.5, NULL, '53010108', 'GAS', NULL, 70.62, NULL, 1110, '2015-06-16 00:00:00', '1899-12-30 08:40:00'),
(31603, NULL, 2.63, 24.4, NULL, '53010108', 'GAS', NULL, 64.42, NULL, 1110, '2015-06-29 00:00:00', '1899-12-30 01:36:00'),
(31914, NULL, 2.69, 25.4, NULL, '53010108', 'GAS', NULL, 68.59, NULL, 1110, '2015-07-10 00:00:00', '1899-12-30 08:16:00'),
(32235, NULL, 2.77, 24.9, NULL, '53010108', 'GAS', NULL, 69.35, NULL, 1110, '2015-07-22 00:00:00', '1899-12-30 09:58:00'),
(32527, NULL, 2.66, 23.3, NULL, '53010108', 'GAS', NULL, 62.22, 'their time is off by at least 30 min.', 1110, '2015-07-30 00:00:00', '1899-12-30 10:11:00'),
(32832, NULL, 2.59, 24.9, NULL, '53010108', 'GAS', NULL, 64.91, NULL, 1110, '2015-08-12 00:00:00', '1899-12-30 06:54:00'),
(33140, NULL, 2.57, 24.4, NULL, '53010108', 'GAS', NULL, 63.04, NULL, 1110, '2015-08-25 00:00:00', '1899-12-30 10:16:00'),
(33452, NULL, 2.35, 25.05, NULL, '53010108', 'GAS', NULL, 59.11, NULL, 1110, '2015-09-09 00:00:00', '1899-12-30 10:21:00'),
(33738, NULL, 2.19, 22.48, NULL, '53010108', 'GAS', NULL, 49.45, NULL, 1110, '2015-09-21 00:00:00', '1899-12-30 07:06:00'),
(34053, NULL, 2.23, 25.12, NULL, '53010108', 'GAS', NULL, 56.26, NULL, 1110, '2015-10-01 00:00:00', '1899-12-30 11:03:00'),
(34346, NULL, 2.29, 23.7, NULL, '53010108', 'GAS', NULL, 54.5, NULL, 1110, '2015-10-14 00:00:00', '1899-12-30 07:39:00'),
(34563, NULL, 2.14, 17.9, NULL, '53010108', 'GAS', NULL, 38.47, NULL, 1110, '2015-10-29 00:00:00', '1899-12-30 05:56:00'),
(34387, NULL, 2.16, 23.3, NULL, '53010108', 'GAS', NULL, 50.73, NULL, 1110, '2015-11-09 00:00:00', '1899-12-30 14:39:52'),
(35158, NULL, 1.97, 25.7, NULL, '53010108', 'GAS', NULL, 50.95, NULL, 1110, '2015-11-20 00:00:00', '1899-12-30 06:28:00'),
(35441, NULL, 1.85, 23.94, NULL, '53010108', 'GAS', NULL, 44.51, NULL, 1110, '2015-12-02 00:00:00', '1899-12-30 12:35:00'),
(35742, NULL, 1.85, 25.14, NULL, '53010108', 'GAS', NULL, 46.74, NULL, 1110, '2015-12-11 00:00:00', '1899-12-30 12:07:00'),
(35982, NULL, 1.77, 21.4, NULL, '53010108', 'GAS', NULL, 38.22, NULL, 1110, '2015-12-23 00:00:00', '1899-12-30 06:43:00'),
(36075, NULL, 1.71, 9.49, NULL, '53010108', 'GAS', NULL, 16.32, NULL, 1110, '2015-12-29 00:00:00', '1899-12-30 07:16:00'),
(36220, NULL, 1.79, 13.7, NULL, '53010108', 'GAS', NULL, 24.68, NULL, 1110, '2016-01-06 00:00:00', '1899-12-30 07:34:00'),
(36406, NULL, 1.74, 15.7, NULL, '53010108', 'GAS', NULL, 27.52, NULL, 1110, '2016-01-13 00:00:00', '1899-12-30 06:56:00'),
(36537, NULL, 1.67, 12.5, NULL, '53010108', 'GAS', NULL, 21, NULL, 1110, '2016-01-19 00:00:00', '1899-12-30 10:12:00'),
(36826, NULL, 1.54, 24.5, NULL, '53010108', 'GAS', NULL, 38.09, 'time stamped on receipt isn''t correct , it was closer to 9 am', 1110, '2016-01-29 00:00:00', '1899-12-30 06:35:00'),
(37109, NULL, 1.42, 24.5, NULL, '53010108', 'GAS', NULL, 35.11, NULL, 1110, '2016-02-11 00:00:00', '1899-12-30 13:13:00'),
(37709, NULL, 1.77, 24, NULL, '53010108', 'GAS', NULL, 42.87, NULL, 1110, '2016-03-07 00:00:00', '1899-12-30 13:31:00'),
(38018, NULL, 1.85, 25.4, NULL, '53010108', 'GAS', NULL, 47.28, NULL, 1110, '2016-03-18 00:00:00', '1899-12-30 13:27:00'),
(38314, NULL, 1.93, 24.9, NULL, '53010108', 'GAS', NULL, 48.32, NULL, 1110, '2016-04-01 00:00:00', '1899-12-30 07:45:00'),
(38622, NULL, 1.86, 25.1, NULL, '53010108', 'GAS', NULL, 47.02, NULL, 1110, '2016-04-11 00:00:00', '1899-12-30 15:10:00'),
(38888, NULL, 2.05, 21.2, NULL, '53010108', 'GAS', NULL, 43.65, NULL, 1110, '2016-04-22 00:00:00', '1899-12-30 06:47:00'),
(39220, NULL, 1.95, 26.08, NULL, '53010108', 'GAS', NULL, 51.11, NULL, 1110, '2016-05-03 00:00:00', '1899-12-30 14:35:00'),
(39524, NULL, 1.93, 23.7, NULL, '53010108', 'GAS', NULL, 46.11, NULL, 1110, '2016-05-12 00:00:00', '1899-12-30 09:44:52'),
(39805, NULL, 2.17, 23.1, NULL, '53010108', 'GAS', NULL, 50.42, NULL, 1110, '2016-05-23 00:00:00', '1899-12-30 07:08:00'),
(40127, NULL, 2.24, 24.8, NULL, '53010108', 'GAS', NULL, 55.94, NULL, 1110, '2016-06-06 00:00:00', '1899-12-30 14:47:00'),
(40428, NULL, 2.31, 24.3, NULL, '53010108', 'GAS', NULL, 56.43, NULL, 1110, '2016-06-17 00:00:00', '1899-12-30 07:08:00'),
(40745, NULL, 2.23, 24.9, NULL, '53010108', 'GAS', NULL, 55.92, NULL, 1110, '2016-06-27 00:00:00', '1899-12-30 16:39:00'),
(41003, NULL, 2.12, 22, NULL, '53010108', 'GAS', NULL, 47, NULL, 1110, '2016-07-11 00:00:00', '1899-12-30 07:12:00'),
(41308, NULL, 2.03, 25.2, NULL, '53010108', 'GAS', NULL, 51.47, NULL, 1110, '2016-07-20 00:00:00', '1899-12-30 12:17:00'),
(41620, NULL, 2.14, 25.35, NULL, '53010108', 'GAS', NULL, 54.49, NULL, 1110, '2016-08-02 00:00:00', '1899-12-30 06:04:00'),
(41897, NULL, 2.21, 21.9, NULL, '53010108', 'GAS', NULL, 48.79, NULL, 1110, '2016-08-15 00:00:00', '1899-12-30 07:19:00'),
(42202, NULL, 2.27, 25.5, NULL, '53010108', 'GAS', NULL, 58.33, NULL, 1110, '2016-08-29 00:00:00', '1899-12-30 13:30:00'),
(42527, NULL, 2.17, 25.6, NULL, '53010108', 'GAS', NULL, 55.81, NULL, 1110, '2016-09-12 00:00:00', '1899-12-30 10:12:00'),
(42829, NULL, 2.15, 24.2, NULL, '53010108', 'GAS', NULL, 52.26, NULL, 1110, '2016-09-21 00:00:00', '1899-12-30 09:21:00'),
(43141, NULL, 2.18, 24.4, NULL, '53010108', 'GAS', NULL, 51.57, NULL, 1110, '2016-09-28 00:00:00', '1899-12-30 12:45:00'),
(21000, NULL, 3.53, 23.738, NULL, NULL, 'GAS', NULL, 84.01, NULL, 1112, '2012-06-11 00:00:00', '1899-12-30 07:00:00'),
(21419, NULL, 3.44, 26.675, NULL, '53010108', 'GAS', NULL, 92, NULL, 1112, '2012-06-28 00:00:00', '1899-12-30 07:16:03'),
(21771, NULL, 3.59, 24.729, NULL, '53010108', 'GAS', NULL, 89, NULL, 1112, '2012-07-05 00:00:00', '1899-12-30 10:00:00'),
(22093, NULL, 3.41, 22.082, NULL, '53010108', 'GAS', NULL, 75.5, NULL, 1112, '2012-07-11 00:00:00', '1899-12-30 06:00:00'),
(22438, NULL, 3.39, 22.361, NULL, '53010108', 'GAS', NULL, 76.01, NULL, 1112, '2012-07-17 00:00:00', '1899-12-30 07:00:00'),
(22825, NULL, 3.44, 27.833, NULL, '53010108', 'GAS', NULL, 96, NULL, 1112, '2012-07-20 00:00:00', '1899-12-30 14:57:00'),
(23200, NULL, 3.36, 24.637, NULL, '53010108', 'GAS', NULL, 83, NULL, 1112, '2012-07-26 00:00:00', '1899-12-30 05:44:00'),
(23570, NULL, 3.53, 25.996, NULL, '53010108', 'GAS', NULL, 92, NULL, 1112, '2012-08-02 00:00:00', '1899-12-30 07:00:00'),
(23928, NULL, 3.85, 24.877, NULL, '53010108', 'GAS', NULL, 96, NULL, 1112, '2012-08-08 00:00:00', '1899-12-30 01:00:00'),
(24285, NULL, 3.83, 24.225, NULL, '53010108', 'GAS', NULL, 93, NULL, 1112, '2012-08-16 00:00:00', '1899-12-30 07:50:00'),
(24654, NULL, 3.77, 24.346, NULL, '53010108', 'GAS', NULL, 92, NULL, 1112, '2012-08-24 00:00:00', '1899-12-30 05:47:00'),
(25035, NULL, 3.93, 25.386, NULL, '53010108', 'GAS', NULL, 100, NULL, 1112, '2012-09-04 00:00:00', '1899-12-30 07:30:58'),
(25410, NULL, 3.9, 26.861, NULL, '53010108', 'GAS', NULL, 105, NULL, 1112, '2012-09-12 00:00:00', '1899-12-30 07:10:00'),
(25837, NULL, 4.02, 25.567, NULL, '53010108', 'GAS', NULL, 103.01, NULL, 1112, '2012-09-19 00:00:00', '1899-12-30 07:37:06'),
(26200, NULL, 3.93, 22.594, NULL, '53010108', 'GAS', NULL, 89, NULL, 1112, '2012-09-27 00:00:00', '1899-12-30 07:51:00'),
(26510, NULL, 3.83, 23.445, NULL, '53010108', 'GAS', NULL, 90.01, NULL, 1112, '2012-10-04 00:00:00', '1899-12-30 07:45:00'),
(26853, NULL, 3.71, 24.204, NULL, '53010108', 'GAS', NULL, 90.01, NULL, 1112, '2012-10-12 00:00:00', '1899-12-30 07:39:00'),
(27230, NULL, 3.61, 25.973, NULL, '53010108', 'GAS', NULL, 94, NULL, 1112, '2012-10-18 00:00:00', '1899-12-30 07:30:00'),
(27640, NULL, 3.47, 29.895, NULL, '53010108', 'GAS', NULL, 104, NULL, 1112, '2012-10-23 00:00:00', '1899-12-30 14:45:19'),
(28035, NULL, 3.27, 27.751, NULL, '53010108', 'GAS', NULL, 91, NULL, 1112, '2012-10-29 00:00:00', '1899-12-30 07:00:00'),
(28396, NULL, 3.27, 26.226, NULL, '53010108', 'GAS', NULL, 86, NULL, 1112, '2012-11-01 00:00:00', '1899-12-30 02:30:00'),
(28795, NULL, 3.37, 28.116, NULL, '53010108', 'GAS', NULL, 95, NULL, 1112, '2012-11-06 00:00:00', '1899-12-30 14:15:00'),
(29138, NULL, 3.35, 24.412, NULL, '53010108', 'GAS', NULL, 82, NULL, 1112, '2012-11-09 00:00:00', '1899-12-30 07:40:55'),
(29550, NULL, 3.47, 29.318, NULL, '53010108', 'GAS', NULL, 102, NULL, 1112, '2012-11-15 00:00:00', '1899-12-30 14:05:32'),
(29950, NULL, 3.53, 28.257, NULL, '53010108', 'GAS', NULL, 100, NULL, 1112, '2012-11-27 00:00:00', '1899-12-30 07:12:09'),
(30315, NULL, 3.41, 26.323, NULL, '53010108', 'GAS', NULL, 90, NULL, 1112, '2012-12-05 00:00:00', '1899-12-30 14:52:53'),
(30650, NULL, 3.23, 25.316, NULL, '53010108', 'GAS', NULL, 82, NULL, 1112, '2012-12-14 00:00:00', '1899-12-30 08:00:00'),
(31008, NULL, 3.25, 18.411, NULL, '53010108', 'GAS', NULL, 60, NULL, 1112, '2012-12-19 00:00:00', '1899-12-30 08:27:46'),
(31250, NULL, 3.21, 24.23, NULL, '53010108', 'GAS', NULL, 78, NULL, 1112, '2012-12-31 00:00:00', '1899-12-30 07:00:00'),
(31566, NULL, 3.17, 26.816, NULL, '53010108', 'GAS', NULL, 85.25, NULL, 1112, '2013-01-08 00:00:00', '1899-12-30 13:45:57'),
(31900, NULL, 3.25, 25.774, NULL, '53010108', 'GAS', NULL, 84, NULL, 1112, '2013-01-15 00:00:00', '1899-12-30 13:50:07'),
(32188, NULL, 3.33, 24.258, NULL, '53010108', 'GAS', NULL, 81, NULL, 1112, '2013-01-22 00:00:00', '1899-12-30 13:39:29'),
(32496, NULL, 3.43, 24.134, NULL, '53010108', 'GAS', NULL, 83, NULL, 1112, '2013-01-28 00:00:00', '1899-12-30 09:00:00'),
(32854, NULL, 3.63, 27.204, NULL, '53010108', 'GAS', NULL, 99, NULL, 1112, '2013-02-01 00:00:00', '1899-12-30 14:32:04'),
(33143, NULL, 3.61, 23.211, NULL, '53010108', 'GAS', NULL, 84, NULL, 1112, '2013-02-08 00:00:00', '1899-12-30 06:00:00'),
(33490, NULL, 3.87, 25.908, NULL, '53010108', 'GAS', NULL, 100.5, NULL, 1112, '2013-02-15 00:00:00', '1899-12-30 14:24:00'),
(33849, NULL, 3.83, 27.09, NULL, '53010108', 'GAS', NULL, 104, NULL, 1112, '2013-02-22 00:00:00', '1899-12-30 12:00:00'),
(34162, NULL, 3.81, 23.305, NULL, '53010108', 'GAS', NULL, 89, NULL, 1112, '2013-03-01 00:00:00', '1899-12-30 07:00:00'),
(34475, NULL, 3.77, 23.287, NULL, '53010108', 'GAS', NULL, 88, NULL, 1112, '2013-03-07 00:00:00', '1899-12-30 07:40:00'),
(34840, NULL, 3.63, 24.731, NULL, '53010108', 'GAS', NULL, 90, NULL, 1112, '2013-03-14 00:00:00', '1899-12-30 08:00:00'),
(35160, NULL, 3.75, 27.666, NULL, '53010108', 'GAS', NULL, 104, NULL, 1112, '2013-03-20 00:00:00', '1899-12-30 13:00:00'),
(35506, NULL, 3.67, 25.007, NULL, '53010108', 'GAS', NULL, 92, NULL, 1112, '2013-03-27 00:00:00', '1899-12-30 13:59:05'),
(35800, NULL, 3.57, 19.978, NULL, '53010108', 'GAS', NULL, 71.5, NULL, 1112, '2013-04-04 00:00:00', '1899-12-30 07:30:00'),
(36100, NULL, 3.53, 22.323, NULL, '53010108', 'GAS', NULL, 79, NULL, 1112, '2013-04-09 00:00:00', '1899-12-30 14:00:00'),
(36432, NULL, 3.47, 22.998, NULL, '53010108', 'GAS', NULL, 80.01, NULL, 1112, '2013-04-16 00:00:00', '1899-12-30 14:57:42'),
(36803, NULL, 3.67, 23.375, NULL, '53010108', 'GAS', NULL, 86, NULL, 1112, '2013-04-22 00:00:00', '1899-12-30 08:00:00'),
(37171, NULL, 3.65, 25.144, NULL, '53010108', 'GAS', NULL, 92, NULL, 1112, '2013-04-29 00:00:00', '1899-12-30 07:00:00'),
(37568, NULL, 3.77, 24.611, NULL, '53010108', 'GAS', NULL, 93, NULL, 1112, '2013-05-06 00:00:00', '1899-12-30 09:00:00'),
(37908, NULL, 3.81, 22.256, NULL, '53010108', 'GAS', NULL, 85, NULL, 1112, '2013-05-13 00:00:00', '1899-12-30 08:00:00'),
(38295, NULL, 3.77, 23.022, NULL, '53010108', 'GAS', NULL, 87, NULL, 1112, '2013-05-17 00:00:00', '1899-12-30 06:20:00'),
(38660, NULL, 3.87, 24.234, NULL, '53010108', 'GAS', NULL, 94, NULL, 1112, '2013-05-24 00:00:00', '1899-12-30 07:00:00'),
(39000, NULL, 4.03, 23.025, NULL, '53010108', 'GAS', NULL, 93, NULL, 1112, '2013-06-11 00:00:00', '1899-12-30 07:15:00'),
(39342, NULL, 3.95, 23.49, NULL, '53010108', 'GAS', NULL, 93, NULL, 1112, '2013-06-17 00:00:00', '1899-12-30 13:00:00'),
(39695, NULL, 3.73, 21.664, NULL, '53010108', 'GAS', NULL, 81, NULL, 1112, '2013-06-24 00:00:00', '1899-12-30 07:15:00'),
(40058, NULL, 3.53, 25.996, NULL, '53010108', 'GAS', NULL, 92, NULL, 1112, '2013-06-28 00:00:00', '1899-12-30 13:49:07'),
(40430, NULL, 3.42, 23.33, NULL, '53010108', 'GAS', NULL, 80, NULL, 1112, '2013-07-09 00:00:00', '1899-12-30 14:41:37'),
(40795, NULL, 3.72, 24.94, NULL, '53010108', 'GAS', NULL, 93, NULL, 1112, '2013-07-16 00:00:00', '1899-12-30 13:31:54'),
(41155, NULL, 3.7, 23.996, NULL, '53010108', 'GAS', NULL, 89, NULL, 1112, '2013-07-23 00:00:00', '1899-12-30 14:14:37'),
(41536, NULL, 3.57, 24.866, NULL, '53010108', 'GAS', NULL, 89, NULL, 1112, '2013-07-31 00:00:00', '1899-12-30 13:27:51'),
(41889, NULL, 3.47, 23.282, NULL, '53010108', 'GAS', NULL, 81, NULL, 1112, '2013-08-09 00:00:00', '1899-12-30 08:00:00'),
(42304, NULL, 3.45, 26.019, NULL, '53010108', 'GAS', NULL, 90, NULL, 1112, '2013-08-16 00:00:00', '1899-12-30 10:30:00'),
(42633, NULL, 3.47, 22.564, NULL, '53010108', 'GAS', NULL, 78.5, NULL, 1112, '2013-08-26 00:00:00', '1899-12-30 09:30:00'),
(42963, NULL, 3.79, 23.006, NULL, '53010108', 'GAS', NULL, 87.4, NULL, 1112, '2013-08-30 00:00:00', '1899-12-30 08:30:00'),
(43325, NULL, 3.57, 24.448, NULL, '53010108', 'GAS', NULL, 87.5, NULL, 1112, '2013-09-10 00:00:00', '1899-12-30 07:20:00'),
(43683, NULL, 3.53, 23.595, NULL, '53010108', 'GAS', NULL, 83.5, NULL, 1112, '2013-09-16 00:00:00', '1899-12-30 06:00:00'),
(44040, NULL, 3.45, 23.127, NULL, '53010108', 'GAS', NULL, 80, NULL, 1112, '2013-09-23 00:00:00', '1899-12-30 08:45:00')
;
select
vehicle_id
, meter
, SUM(meter) over(partition by vehicle_id order by date_trans, time_trans) as sum_meter
, lead(meter) over(partition by vehicle_id order by date_trans, time_trans) as lead_meter
, date_trans
, time_trans
from @fuel_transaction
order by
vehicle_id
, date_trans
, time_trans
The purpose of the comment above is that you most probably do not need the CTE approach you use now, instead you should be be able to use Analytic Functions LEAD() or LAG() and perhaps SUM()
see
https://msdn.microsoft.com/en-us/library/hh213125.aspx LEAD()
https://msdn.microsoft.com/en-us/library/hh231256.aspx LAG()
https://msdn.microsoft.com/en-us/library/ms189461.aspx OVER()
see
https://msdn.microsoft.com/en-us/library/hh213125.aspx LEAD()
https://msdn.microsoft.com/en-us/library/hh231256.aspx LAG()
https://msdn.microsoft.com/en-us/library/ms189461.aspx OVER()
Compare this result to your expected result
+-----+------------+-------+-------+--------------+---------------+-------------+------------+--------------------------+---------------------+---------------------+
| | vehicle_id | pm | cm | driven_miles | cost_per_unit | total_units | cost_total | MPG | date_trans | time_trans |
+-----+------------+-------+-------+--------------+---------------+-------------+------------+--------------------------+---------------------+---------------------+
| 1 | 1109 | 0 | 68797 | 68797 | 2 | 34 | 34 | 2023,4411764705882352941 | 06.01.2016 00:00:00 | 30.12.1899 02:41:00 |
| 2 | 1109 | 68797 | 68984 | 187 | 2 | 17 | 29 | 11,0000000000000000000 | 13.01.2016 00:00:00 | 30.12.1899 11:43:00 |
| 3 | 1109 | 68984 | 69209 | 225 | 2 | 22 | 36 | 10,2272727272727272727 | 26.01.2016 00:00:00 | 30.12.1899 12:01:00 |
| 4 | 1109 | 69209 | 69382 | 173 | 2 | 13 | 22 | 13,3076923076923076923 | 29.01.2016 00:00:00 | 30.12.1899 12:33:00 |
| 5 | 1109 | 69382 | 69525 | 143 | 2 | 14 | 23 | 10,2142857142857142857 | 04.02.2016 00:00:00 | 30.12.1899 16:13:00 |
| 6 | 1109 | 69525 | 69691 | 166 | 2 | 16 | 24 | 10,3750000000000000000 | 12.02.2016 00:00:00 | 30.12.1899 12:25:00 |
| 7 | 1109 | 69691 | 69843 | 152 | 2 | 15 | 22 | 10,1333333333333333333 | 23.02.2016 00:00:00 | 30.12.1899 07:41:00 |
| 8 | 1109 | 69843 | 70232 | 389 | 2 | 12 | 21 | 32,4166666666666666666 | 01.03.2016 00:00:00 | 30.12.1899 19:15:00 |
| 9 | 1109 | 70232 | 70428 | 196 | 2 | 17 | 30 | 11,5294117647058823529 | 07.03.2016 00:00:00 | 30.12.1899 09:51:00 |
| 10 | 1109 | 70428 | 70685 | 257 | 2 | 23 | 44 | 11,1739130434782608695 | 23.03.2016 00:00:00 | 30.12.1899 10:03:00 |
| 11 | 1109 | 70685 | 70840 | 155 | 2 | 14 | 26 | 11,0714285714285714285 | 29.03.2016 00:00:00 | 30.12.1899 06:28:00 |
| 12 | 1109 | 70840 | 71253 | 413 | 2 | 20 | 37 | 20,6500000000000000000 | 03.04.2016 00:00:00 | 30.12.1899 18:28:00 |
| 13 | 1109 | 71253 | 71469 | 216 | 2 | 18 | 35 | 12,0000000000000000000 | 12.04.2016 00:00:00 | 30.12.1899 15:47:00 |
| 14 | 1109 | 71469 | 71756 | 287 | 2 | 25 | 54 | 11,4800000000000000000 | 17.04.2016 00:00:00 | 30.12.1899 15:38:00 |
| 15 | 1109 | 71756 | 72007 | 251 | 2 | 17 | 36 | 14,7647058823529411764 | 22.04.2016 00:00:00 | 30.12.1899 11:02:37 |
| 16 | 1109 | 72007 | 72266 | 259 | 2 | 18 | 39 | 14,3888888888888888888 | 29.04.2016 00:00:00 | 30.12.1899 11:09:30 |
| 17 | 1109 | 72266 | 72348 | 82 | 2 | 27 | 53 | 3,0370370370370370370 | 05.05.2016 00:00:00 | 30.12.1899 16:03:00 |
| 18 | 1109 | 72348 | 72623 | 275 | 2 | 25 | 52 | 11,0000000000000000000 | 17.05.2016 00:00:00 | 30.12.1899 05:26:00 |
| 19 | 1109 | 72623 | 72748 | 125 | 2 | 9 | 19 | 13,8888888888888888888 | 21.05.2016 00:00:00 | 30.12.1899 10:23:09 |
| 20 | 1109 | 72748 | 73040 | 292 | 2 | 24 | 53 | 12,1666666666666666666 | 01.06.2016 00:00:00 | 30.12.1899 16:38:00 |
| 21 | 1109 | 73040 | 73201 | 161 | 2 | 15 | 32 | 10,7333333333333333333 | 14.06.2016 00:00:00 | 30.12.1899 18:09:00 |
| 22 | 1109 | 73201 | 73379 | 178 | 2 | 17 | 40 | 10,4705882352941176470 | 24.06.2016 00:00:00 | 30.12.1899 07:49:46 |
| 23 | 1109 | 73379 | 73716 | 337 | 2 | 28 | 60 | 12,0357142857142857142 | 28.06.2016 00:00:00 | 30.12.1899 14:33:00 |
| 24 | 1109 | 73716 | 73873 | 157 | 2 | 14 | 29 | 11,2142857142857142857 | 11.07.2016 00:00:00 | 30.12.1899 12:01:00 |
| 25 | 1109 | 73873 | 74180 | 307 | 2 | 25 | 53 | 12,2800000000000000000 | 13.07.2016 00:00:00 | 30.12.1899 07:09:00 |
| 26 | 1109 | 74180 | 74458 | 278 | 2 | 23 | 47 | 12,0869565217391304347 | 18.07.2016 00:00:00 | 30.12.1899 13:07:00 |
| 27 | 1109 | 74458 | 74650 | 192 | 2 | 16 | 33 | 12,0000000000000000000 | 19.07.2016 00:00:00 | 30.12.1899 13:49:00 |
| 28 | 1109 | 74650 | 74833 | 183 | 2 | 5 | 10 | 36,6000000000000000000 | 26.07.2016 00:00:00 | 30.12.1899 06:25:00 |
| 29 | 1109 | 74833 | 74834 | 1 | 2 | 12 | 24 | 0,0833333333333333333 | 26.07.2016 00:00:00 | 30.12.1899 06:29:00 |
| 30 | 1109 | 74834 | 75058 | 224 | 2 | 24 | 48 | 9,3333333333333333333 | 29.07.2016 00:00:00 | 30.12.1899 22:04:00 |
| 31 | 1109 | 75058 | 75260 | 202 | 2 | 20 | 48 | 10,1000000000000000000 | 06.08.2016 00:00:00 | 30.12.1899 06:52:00 |
| 32 | 1109 | 75260 | 75449 | 189 | 2 | 13 | 27 | 14,5384615384615384615 | 08.08.2016 00:00:00 | 30.12.1899 13:43:00 |
| 33 | 1109 | 75449 | 76609 | 1160 | 2 | 13 | 27 | 89,2307692307692307692 | 10.08.2016 00:00:00 | 30.12.1899 14:32:00 |
| 34 | 1109 | 76609 | 75825 | 0 | 2 | 21 | 48 | 0,0000000000000000000 | 19.08.2016 00:00:00 | 30.12.1899 11:00:00 |
| 35 | 1109 | 75825 | 76097 | 272 | 2 | 23 | 49 | 11,8260869565217391304 | 31.08.2016 00:00:00 | 30.12.1899 11:13:00 |
| 36 | 1109 | 76097 | 76283 | 186 | 2 | 16 | 32 | 11,6250000000000000000 | 10.09.2016 00:00:00 | 30.12.1899 21:37:00 |
| 37 | 1109 | 76283 | 76539 | 256 | 2 | 22 | 46 | 11,6363636363636363636 | 13.09.2016 00:00:00 | 30.12.1899 12:36:00 |
| 38 | 1109 | 76539 | 76747 | 208 | 2 | 17 | 33 | 12,2352941176470588235 | 19.09.2016 00:00:00 | 30.12.1899 13:16:00 |
| 39 | 1109 | 76747 | 76894 | 147 | 2 | 13 | 26 | 11,3076923076923076923 | 23.09.2016 00:00:00 | 30.12.1899 11:58:00 |
| 40 | 1109 | 76894 | 77180 | 286 | 2 | 26 | 61 | 11,0000000000000000000 | 07.10.2016 00:00:00 | 30.12.1899 14:21:00 |
| 41 | 1109 | 77180 | 77473 | 293 | 2 | 22 | 46 | 13,3181818181818181818 | 14.10.2016 00:00:00 | 30.12.1899 06:35:05 |
| 42 | 1109 | 77473 | 77656 | 183 | 2 | 16 | 33 | 11,4375000000000000000 | 24.10.2016 00:00:00 | 30.12.1899 10:13:00 |
| 43 | 1109 | 77656 | 77890 | 234 | 2 | 20 | 41 | 11,7000000000000000000 | 01.11.2016 00:00:00 | 30.12.1899 14:39:00 |
| 44 | 1109 | 77890 | 78049 | 159 | 2 | 15 | 28 | 10,6000000000000000000 | 15.11.2016 00:00:00 | 30.12.1899 15:09:00 |
| 45 | 1110 | 0 | 10198 | 10198 | 3 | 22 | 71 | 463,5454545454545454545 | 07.06.2012 00:00:00 | 30.12.1899 13:27:07 |
| 46 | 1110 | 10198 | 10353 | 155 | 4 | 22 | 77 | 7,0454545454545454545 | 25.06.2012 00:00:00 | 30.12.1899 07:35:00 |
| 47 | 1110 | 10353 | 10785 | 432 | 3 | 25 | 85 | 17,2800000000000000000 | 04.07.2012 00:00:00 | 30.12.1899 07:37:00 |
| 48 | 1110 | 10785 | 11055 | 270 | 3 | 23 | 77 | 11,7391304347826086956 | 20.07.2012 00:00:00 | 30.12.1899 14:37:00 |
| 49 | 1110 | 11055 | 11440 | 385 | 3 | 26 | 87 | 14,8076923076923076923 | 27.07.2012 00:00:00 | 30.12.1899 08:07:59 |
| 50 | 1110 | 11440 | 11673 | 233 | 4 | 23 | 84 | 10,1304347826086956521 | 09.08.2012 00:00:00 | 30.12.1899 09:34:52 |
| 51 | 1110 | 11673 | 11903 | 230 | 4 | 23 | 83 | 10,0000000000000000000 | 21.08.2012 00:00:00 | 30.12.1899 07:04:00 |
| 52 | 1110 | 11903 | 13032 | 1129 | 4 | 24 | 91 | 47,0416666666666666666 | 27.09.2012 00:00:00 | 30.12.1899 09:22:31 |
| 53 | 1110 | 13032 | 13850 | 818 | 3 | 24 | 70 | 34,0833333333333333333 | 24.12.2012 00:00:00 | 30.12.1899 11:28:33 |
| 54 | 1110 | 13850 | 14683 | 833 | 3 | 22 | 75 | 37,8636363636363636363 | 01.01.2013 00:00:00 | 30.12.1899 12:00:42 |
| 55 | 1110 | 14683 | 14913 | 230 | 3 | 24 | 70 | 9,5833333333333333333 | 09.01.2013 00:00:00 | 30.12.1899 10:23:31 |
| 56 | 1110 | 14913 | 15113 | 200 | 3 | 16 | 51 | 12,5000000000000000000 | 26.01.2013 00:00:00 | 30.12.1899 09:23:00 |
| 57 | 1110 | 15113 | 15337 | 224 | 4 | 19 | 72 | 11,7894736842105263157 | 18.02.2013 00:00:00 | 30.12.1899 07:16:00 |
| 58 | 1110 | 15337 | 15545 | 208 | 4 | 21 | 78 | 9,9047619047619047619 | 05.03.2013 00:00:00 | 30.12.1899 09:06:00 |
| 59 | 1110 | 15545 | 15763 | 218 | 3 | 21 | 71 | 10,3809523809523809523 | 21.03.2013 00:00:00 | 30.12.1899 08:57:00 |
| 60 | 1110 | 15763 | 16015 | 252 | 3 | 22 | 77 | 11,4545454545454545454 | 04.04.2013 00:00:00 | 30.12.1899 08:19:00 |
| 61 | 1110 | 16015 | 16201 | 186 | 3 | 22 | 73 | 8,4545454545454545454 | 19.04.2013 00:00:00 | 30.12.1899 08:24:00 |
| 62 | 1110 | 16201 | 16573 | 372 | 3 | 24 | 82 | 15,5000000000000000000 | 06.05.2013 00:00:00 | 30.12.1899 07:12:00 |
| 63 | 1110 | 16573 | 16896 | 323 | 4 | 25 | 98 | 12,9200000000000000000 | 16.05.2013 00:00:00 | 30.12.1899 11:16:00 |
| 64 | 1110 | 16896 | 17206 | 310 | 4 | 24 | 88 | 12,9166666666666666666 | 31.05.2013 00:00:00 | 30.12.1899 12:58:00 |
| 65 | 1110 | 17206 | 17482 | 276 | 4 | 22 | 76 | 12,5454545454545454545 | 17.06.2013 00:00:00 | 30.12.1899 06:20:00 |
| 66 | 1110 | 17482 | 17767 | 285 | 4 | 23 | 81 | 12,3913043478260869565 | 15.07.2013 00:00:00 | 30.12.1899 09:18:00 |
| 67 | 1110 | 17767 | 18061 | 294 | 4 | 25 | 91 | 11,7600000000000000000 | 29.07.2013 00:00:00 | 30.12.1899 12:18:00 |
| 68 | 1110 | 18061 | 18339 | 278 | 4 | 23 | 83 | 12,0869565217391304347 | 08.08.2013 00:00:00 | 30.12.1899 12:25:00 |
| 69 | 1110 | 18339 | 18546 | 207 | 4 | 17 | 59 | 12,1764705882352941176 | 21.08.2013 00:00:00 | 30.12.1899 12:16:00 |
| 70 | 1110 | 18546 | 18856 | 310 | 4 | 25 | 89 | 12,4000000000000000000 | 06.09.2013 00:00:00 | 30.12.1899 11:13:00 |
| 71 | 1110 | 18856 | 19136 | 280 | 4 | 23 | 81 | 12,1739130434782608695 | 18.09.2013 00:00:00 | 30.12.1899 11:08:00 |
| 72 | 1110 | 19136 | 19568 | 432 | 3 | 9 | 26 | 48,0000000000000000000 | 21.10.2013 00:00:00 | 30.12.1899 13:24:00 |
| 73 | 1110 | 19568 | 19785 | 217 | 3 | 20 | 63 | 10,8500000000000000000 | 23.10.2013 00:00:00 | 30.12.1899 11:07:00 |
| 74 | 1110 | 19785 | 20421 | 636 | 3 | 26 | 76 | 24,4615384615384615384 | 15.11.2013 00:00:00 | 30.12.1899 06:24:00 |
| 75 | 1110 | 20421 | 20674 | 253 | 3 | 23 | 67 | 11,0000000000000000000 | 03.12.2013 00:00:00 | 30.12.1899 10:50:00 |
| 76 | 1110 | 20674 | 20943 | 269 | 3 | 24 | 71 | 11,2083333333333333333 | 13.12.2013 00:00:00 | 30.12.1899 10:20:00 |
| 77 | 1110 | 20943 | 21190 | 247 | 3 | 22 | 68 | 11,2272727272727272727 | 03.01.2014 00:00:00 | 30.12.1899 07:33:00 |
| 78 | 1110 | 21190 | 21413 | 223 | 3 | 23 | 71 | 9,6956521739130434782 | 20.01.2014 00:00:00 | 30.12.1899 11:27:00 |
| 79 | 1110 | 21413 | 21646 | 233 | 3 | 22 | 67 | 10,5909090909090909090 | 30.01.2014 00:00:00 | 30.12.1899 14:25:00 |
| 80 | 1110 | 21646 | 21702 | 56 | 3 | 10 | 32 | 5,6000000000000000000 | 04.02.2014 00:00:00 | 30.12.1899 09:38:00 |
| 81 | 1110 | 21702 | 21972 | 270 | 3 | 24 | 80 | 11,2500000000000000000 | 24.02.2014 00:00:00 | 30.12.1899 07:30:00 |
| 82 | 1110 | 21972 | 22481 | 509 | 3 | 23 | 78 | 22,1304347826086956521 | 28.03.2014 00:00:00 | 30.12.1899 09:00:00 |
| 83 | 1110 | 22481 | 23071 | 590 | 4 | 23 | 79 | 25,6521739130434782608 | 02.05.2014 00:00:00 | 30.12.1899 11:34:00 |
| 84 | 1110 | 23071 | 23389 | 318 | 3 | 24 | 84 | 13,2500000000000000000 | 13.05.2014 00:00:00 | 30.12.1899 13:46:00 |
| 85 | 1110 | 23389 | 23693 | 304 | 4 | 25 | 90 | 12,1600000000000000000 | 29.05.2014 00:00:00 | 30.12.1899 11:04:00 |
| 86 | 1110 | 23693 | 23957 | 264 | 4 | 22 | 79 | 12,0000000000000000000 | 17.06.2014 00:00:00 | 30.12.1899 10:32:00 |
| 87 | 1110 | 23957 | 24259 | 302 | 4 | 25 | 90 | 12,0800000000000000000 | 07.07.2014 00:00:00 | 30.12.1899 12:16:00 |
| 88 | 1110 | 24259 | 24538 | 279 | 3 | 23 | 79 | 12,1304347826086956521 | 24.07.2014 00:00:00 | 30.12.1899 11:20:00 |
| 89 | 1110 | 24538 | 25139 | 601 | 3 | 25 | 84 | 24,0400000000000000000 | 25.08.2014 00:00:00 | 30.12.1899 04:11:00 |
| 90 | 1110 | 25139 | 25426 | 287 | 3 | 16 | 51 | 17,9375000000000000000 | 27.08.2014 00:00:00 | 30.12.1899 03:55:00 |
| 91 | 1110 | 25426 | 25198 | 0 | 3 | 6 | 19 | 0,0000000000000000000 | 27.08.2014 00:00:00 | 30.12.1899 07:14:00 |
| 92 | 1110 | 25198 | 25759 | 561 | 3 | 26 | 85 | 21,5769230769230769230 | 02.09.2014 00:00:00 | 30.12.1899 06:17:00 |
| 93 | 1110 | 25759 | 26067 | 308 | 3 | 26 | 84 | 11,8461538461538461538 | 18.09.2014 00:00:00 | 30.12.1899 06:45:00 |
| 94 | 1110 | 26067 | 26666 | 599 | 3 | 24 | 70 | 24,9583333333333333333 | 27.10.2014 00:00:00 | 30.12.1899 11:22:00 |
| 95 | 1110 | 26666 | 26924 | 258 | 3 | 23 | 64 | 11,2173913043478260869 | 12.11.2014 00:00:00 | 30.12.1899 08:22:00 |
| 96 | 1110 | 26924 | 27107 | 183 | 3 | 18 | 48 | 10,1666666666666666666 | 21.11.2014 00:00:00 | 30.12.1899 07:32:00 |
| 97 | 1110 | 27107 | 27373 | 266 | 2 | 25 | 59 | 10,6400000000000000000 | 12.12.2014 00:00:00 | 30.12.1899 06:32:00 |
| 98 | 1110 | 27373 | 27609 | 236 | 2 | 20 | 40 | 11,8000000000000000000 | 23.12.2014 00:00:00 | 30.12.1899 08:40:00 |
| 99 | 1110 | 27609 | 27839 | 230 | 2 | 24 | 41 | 9,5833333333333333333 | 19.01.2015 00:00:00 | 30.12.1899 07:18:00 |
| 100 | 1110 | 27839 | 28061 | 222 | 2 | 19 | 38 | 11,6842105263157894736 | 29.01.2015 00:00:00 | 30.12.1899 08:27:00 |
| 101 | 1110 | 28061 | 28366 | 305 | 2 | 5 | 10 | 61,0000000000000000000 | 16.02.2015 00:00:00 | 30.12.1899 09:53:00 |
| 102 | 1110 | 28366 | 28544 | 178 | 2 | 17 | 39 | 10,4705882352941176470 | 18.02.2015 00:00:00 | 30.12.1899 01:40:00 |
| 103 | 1110 | 28544 | 28890 | 346 | 2 | 25 | 55 | 13,8400000000000000000 | 25.02.2015 00:00:00 | 30.12.1899 14:03:00 |
| 104 | 1110 | 28890 | 29169 | 279 | 2 | 24 | 56 | 11,6250000000000000000 | 09.03.2015 00:00:00 | 30.12.1899 09:44:00 |
| 105 | 1110 | 29169 | 29459 | 290 | 2 | 24 | 53 | 12,0833333333333333333 | 26.03.2015 00:00:00 | 30.12.1899 07:52:00 |
| 106 | 1110 | 29459 | 29775 | 316 | 2 | 25 | 57 | 12,6400000000000000000 | 08.04.2015 00:00:00 | 30.12.1899 07:21:00 |
| 107 | 1110 | 29775 | 30371 | 596 | 2 | 23 | 54 | 25,9130434782608695652 | 30.04.2015 00:00:00 | 30.12.1899 06:35:00 |
| 108 | 1110 | 30371 | 30987 | 616 | 3 | 25 | 66 | 24,6400000000000000000 | 05.06.2015 00:00:00 | 30.12.1899 07:36:00 |
| 109 | 1110 | 30987 | 31306 | 319 | 3 | 26 | 70 | 12,2692307692307692307 | 16.06.2015 00:00:00 | 30.12.1899 08:40:00 |
| 110 | 1110 | 31306 | 31603 | 297 | 3 | 24 | 64 | 12,3750000000000000000 | 29.06.2015 00:00:00 | 30.12.1899 01:36:00 |
| 111 | 1110 | 31603 | 31914 | 311 | 3 | 25 | 68 | 12,4400000000000000000 | 10.07.2015 00:00:00 | 30.12.1899 08:16:00 |
| 112 | 1110 | 31914 | 32235 | 321 | 3 | 25 | 69 | 12,8400000000000000000 | 22.07.2015 00:00:00 | 30.12.1899 09:58:00 |
| 113 | 1110 | 32235 | 32527 | 292 | 3 | 23 | 62 | 12,6956521739130434782 | 30.07.2015 00:00:00 | 30.12.1899 10:11:00 |
| 114 | 1110 | 32527 | 32832 | 305 | 3 | 25 | 64 | 12,2000000000000000000 | 12.08.2015 00:00:00 | 30.12.1899 06:54:00 |
| 115 | 1110 | 32832 | 33140 | 308 | 3 | 24 | 63 | 12,8333333333333333333 | 25.08.2015 00:00:00 | 30.12.1899 10:16:00 |
| 116 | 1110 | 33140 | 33452 | 312 | 2 | 25 | 59 | 12,4800000000000000000 | 09.09.2015 00:00:00 | 30.12.1899 10:21:00 |
| 117 | 1110 | 33452 | 33738 | 286 | 2 | 22 | 49 | 13,0000000000000000000 | 21.09.2015 00:00:00 | 30.12.1899 07:06:00 |
| 118 | 1110 | 33738 | 34053 | 315 | 2 | 25 | 56 | 12,6000000000000000000 | 01.10.2015 00:00:00 | 30.12.1899 11:03:00 |
| 119 | 1110 | 34053 | 34346 | 293 | 2 | 24 | 54 | 12,2083333333333333333 | 14.10.2015 00:00:00 | 30.12.1899 07:39:00 |
| 120 | 1110 | 34346 | 34563 | 217 | 2 | 18 | 38 | 12,0555555555555555555 | 29.10.2015 00:00:00 | 30.12.1899 05:56:00 |
| 121 | 1110 | 34563 | 34387 | 0 | 2 | 23 | 50 | 0,0000000000000000000 | 09.11.2015 00:00:00 | 30.12.1899 14:39:52 |
| 122 | 1110 | 34387 | 35158 | 771 | 2 | 26 | 50 | 29,6538461538461538461 | 20.11.2015 00:00:00 | 30.12.1899 06:28:00 |
| 123 | 1110 | 35158 | 35441 | 283 | 2 | 24 | 44 | 11,7916666666666666666 | 02.12.2015 00:00:00 | 30.12.1899 12:35:00 |
| 124 | 1110 | 35441 | 35742 | 301 | 2 | 25 | 46 | 12,0400000000000000000 | 11.12.2015 00:00:00 | 30.12.1899 12:07:00 |
| 125 | 1110 | 35742 | 35982 | 240 | 2 | 21 | 38 | 11,4285714285714285714 | 23.12.2015 00:00:00 | 30.12.1899 06:43:00 |
| 126 | 1110 | 35982 | 36075 | 93 | 2 | 9 | 16 | 10,3333333333333333333 | 29.12.2015 00:00:00 | 30.12.1899 07:16:00 |
| 127 | 1110 | 36075 | 36220 | 145 | 2 | 14 | 24 | 10,3571428571428571428 | 06.01.2016 00:00:00 | 30.12.1899 07:34:00 |
| 128 | 1110 | 36220 | 36406 | 186 | 2 | 16 | 27 | 11,6250000000000000000 | 13.01.2016 00:00:00 | 30.12.1899 06:56:00 |
| 129 | 1110 | 36406 | 36537 | 131 | 2 | 13 | 21 | 10,0769230769230769230 | 19.01.2016 00:00:00 | 30.12.1899 10:12:00 |
| 130 | 1110 | 36537 | 36826 | 289 | 2 | 25 | 38 | 11,5600000000000000000 | 29.01.2016 00:00:00 | 30.12.1899 06:35:00 |
| 131 | 1110 | 36826 | 37109 | 283 | 1 | 25 | 35 | 11,3200000000000000000 | 11.02.2016 00:00:00 | 30.12.1899 13:13:00 |
| 132 | 1110 | 37109 | 37709 | 600 | 2 | 24 | 42 | 25,0000000000000000000 | 07.03.2016 00:00:00 | 30.12.1899 13:31:00 |
| 133 | 1110 | 37709 | 38018 | 309 | 2 | 25 | 47 | 12,3600000000000000000 | 18.03.2016 00:00:00 | 30.12.1899 13:27:00 |
| 134 | 1110 | 38018 | 38314 | 296 | 2 | 25 | 48 | 11,8400000000000000000 | 01.04.2016 00:00:00 | 30.12.1899 07:45:00 |
| 135 | 1110 | 38314 | 38622 | 308 | 2 | 25 | 47 | 12,3200000000000000000 | 11.04.2016 00:00:00 | 30.12.1899 15:10:00 |
| 136 | 1110 | 38622 | 38888 | 266 | 2 | 21 | 43 | 12,6666666666666666666 | 22.04.2016 00:00:00 | 30.12.1899 06:47:00 |
| 137 | 1110 | 38888 | 39220 | 332 | 2 | 26 | 51 | 12,7692307692307692307 | 03.05.2016 00:00:00 | 30.12.1899 14:35:00 |
| 138 | 1110 | 39220 | 39524 | 304 | 2 | 24 | 46 | 12,6666666666666666666 | 12.05.2016 00:00:00 | 30.12.1899 09:44:52 |
| 139 | 1110 | 39524 | 39805 | 281 | 2 | 23 | 50 | 12,2173913043478260869 | 23.05.2016 00:00:00 | 30.12.1899 07:08:00 |
| 140 | 1110 | 39805 | 40127 | 322 | 2 | 25 | 55 | 12,8800000000000000000 | 06.06.2016 00:00:00 | 30.12.1899 14:47:00 |
| 141 | 1110 | 40127 | 40428 | 301 | 2 | 24 | 56 | 12,5416666666666666666 | 17.06.2016 00:00:00 | 30.12.1899 07:08:00 |
| 142 | 1110 | 40428 | 40745 | 317 | 2 | 25 | 55 | 12,6800000000000000000 | 27.06.2016 00:00:00 | 30.12.1899 16:39:00 |
| 143 | 1110 | 40745 | 41003 | 258 | 2 | 22 | 47 | 11,7272727272727272727 | 11.07.2016 00:00:00 | 30.12.1899 07:12:00 |
| 144 | 1110 | 41003 | 41308 | 305 | 2 | 25 | 51 | 12,2000000000000000000 | 20.07.2016 00:00:00 | 30.12.1899 12:17:00 |
| 145 | 1110 | 41308 | 41620 | 312 | 2 | 25 | 54 | 12,4800000000000000000 | 02.08.2016 00:00:00 | 30.12.1899 06:04:00 |
| 146 | 1110 | 41620 | 41897 | 277 | 2 | 22 | 48 | 12,5909090909090909090 | 15.08.2016 00:00:00 | 30.12.1899 07:19:00 |
| 147 | 1110 | 41897 | 42202 | 305 | 2 | 26 | 58 | 11,7307692307692307692 | 29.08.2016 00:00:00 | 30.12.1899 13:30:00 |
| 148 | 1110 | 42202 | 42527 | 325 | 2 | 26 | 55 | 12,5000000000000000000 | 12.09.2016 00:00:00 | 30.12.1899 10:12:00 |
| 149 | 1110 | 42527 | 42829 | 302 | 2 | 24 | 52 | 12,5833333333333333333 | 21.09.2016 00:00:00 | 30.12.1899 09:21:00 |
| 150 | 1110 | 42829 | 43141 | 312 | 2 | 24 | 51 | 13,0000000000000000000 | 28.09.2016 00:00:00 | 30.12.1899 12:45:00 |
| 151 | 1112 | 0 | 21000 | 21000 | 4 | 24 | 84 | 875,0000000000000000000 | 11.06.2012 00:00:00 | 30.12.1899 07:00:00 |
| 152 | 1112 | 21000 | 21419 | 419 | 3 | 27 | 92 | 15,5185185185185185185 | 28.06.2012 00:00:00 | 30.12.1899 07:16:03 |
| 153 | 1112 | 21419 | 21771 | 352 | 4 | 25 | 89 | 14,0800000000000000000 | 05.07.2012 00:00:00 | 30.12.1899 10:00:00 |
| 154 | 1112 | 21771 | 22093 | 322 | 3 | 22 | 75 | 14,6363636363636363636 | 11.07.2012 00:00:00 | 30.12.1899 06:00:00 |
| 155 | 1112 | 22093 | 22438 | 345 | 3 | 22 | 76 | 15,6818181818181818181 | 17.07.2012 00:00:00 | 30.12.1899 07:00:00 |
| 156 | 1112 | 22438 | 22825 | 387 | 3 | 28 | 96 | 13,8214285714285714285 | 20.07.2012 00:00:00 | 30.12.1899 14:57:00 |
| 157 | 1112 | 22825 | 23200 | 375 | 3 | 25 | 83 | 15,0000000000000000000 | 26.07.2012 00:00:00 | 30.12.1899 05:44:00 |
| 158 | 1112 | 23200 | 23570 | 370 | 4 | 26 | 92 | 14,2307692307692307692 | 02.08.2012 00:00:00 | 30.12.1899 07:00:00 |
| 159 | 1112 | 23570 | 23928 | 358 | 4 | 25 | 96 | 14,3200000000000000000 | 08.08.2012 00:00:00 | 30.12.1899 01:00:00 |
| 160 | 1112 | 23928 | 24285 | 357 | 4 | 24 | 93 | 14,8750000000000000000 | 16.08.2012 00:00:00 | 30.12.1899 07:50:00 |
| 161 | 1112 | 24285 | 24654 | 369 | 4 | 24 | 92 | 15,3750000000000000000 | 24.08.2012 00:00:00 | 30.12.1899 05:47:00 |
| 162 | 1112 | 24654 | 25035 | 381 | 4 | 25 | 100 | 15,2400000000000000000 | 04.09.2012 00:00:00 | 30.12.1899 07:30:58 |
| 163 | 1112 | 25035 | 25410 | 375 | 4 | 27 | 105 | 13,8888888888888888888 | 12.09.2012 00:00:00 | 30.12.1899 07:10:00 |
| 164 | 1112 | 25410 | 25837 | 427 | 4 | 26 | 103 | 16,4230769230769230769 | 19.09.2012 00:00:00 | 30.12.1899 07:37:06 |
| 165 | 1112 | 25837 | 26200 | 363 | 4 | 23 | 89 | 15,7826086956521739130 | 27.09.2012 00:00:00 | 30.12.1899 07:51:00 |
| 166 | 1112 | 26200 | 26510 | 310 | 4 | 23 | 90 | 13,4782608695652173913 | 04.10.2012 00:00:00 | 30.12.1899 07:45:00 |
| 167 | 1112 | 26510 | 26853 | 343 | 4 | 24 | 90 | 14,2916666666666666666 | 12.10.2012 00:00:00 | 30.12.1899 07:39:00 |
| 168 | 1112 | 26853 | 27230 | 377 | 4 | 26 | 94 | 14,5000000000000000000 | 18.10.2012 00:00:00 | 30.12.1899 07:30:00 |
| 169 | 1112 | 27230 | 27640 | 410 | 3 | 30 | 104 | 13,6666666666666666666 | 23.10.2012 00:00:00 | 30.12.1899 14:45:19 |
| 170 | 1112 | 27640 | 28035 | 395 | 3 | 28 | 91 | 14,1071428571428571428 | 29.10.2012 00:00:00 | 30.12.1899 07:00:00 |
| 171 | 1112 | 28035 | 28396 | 361 | 3 | 26 | 86 | 13,8846153846153846153 | 01.11.2012 00:00:00 | 30.12.1899 02:30:00 |
| 172 | 1112 | 28396 | 28795 | 399 | 3 | 28 | 95 | 14,2500000000000000000 | 06.11.2012 00:00:00 | 30.12.1899 14:15:00 |
| 173 | 1112 | 28795 | 29138 | 343 | 3 | 24 | 82 | 14,2916666666666666666 | 09.11.2012 00:00:00 | 30.12.1899 07:40:55 |
| 174 | 1112 | 29138 | 29550 | 412 | 3 | 29 | 102 | 14,2068965517241379310 | 15.11.2012 00:00:00 | 30.12.1899 14:05:32 |
| 175 | 1112 | 29550 | 29950 | 400 | 4 | 28 | 100 | 14,2857142857142857142 | 27.11.2012 00:00:00 | 30.12.1899 07:12:09 |
| 176 | 1112 | 29950 | 30315 | 365 | 3 | 26 | 90 | 14,0384615384615384615 | 05.12.2012 00:00:00 | 30.12.1899 14:52:53 |
| 177 | 1112 | 30315 | 30650 | 335 | 3 | 25 | 82 | 13,4000000000000000000 | 14.12.2012 00:00:00 | 30.12.1899 08:00:00 |
| 178 | 1112 | 30650 | 31008 | 358 | 3 | 18 | 60 | 19,8888888888888888888 | 19.12.2012 00:00:00 | 30.12.1899 08:27:46 |
| 179 | 1112 | 31008 | 31250 | 242 | 3 | 24 | 78 | 10,0833333333333333333 | 31.12.2012 00:00:00 | 30.12.1899 07:00:00 |
| 180 | 1112 | 31250 | 31566 | 316 | 3 | 27 | 85 | 11,7037037037037037037 | 08.01.2013 00:00:00 | 30.12.1899 13:45:57 |
| 181 | 1112 | 31566 | 31900 | 334 | 3 | 26 | 84 | 12,8461538461538461538 | 15.01.2013 00:00:00 | 30.12.1899 13:50:07 |
| 182 | 1112 | 31900 | 32188 | 288 | 3 | 24 | 81 | 12,0000000000000000000 | 22.01.2013 00:00:00 | 30.12.1899 13:39:29 |
| 183 | 1112 | 32188 | 32496 | 308 | 3 | 24 | 83 | 12,8333333333333333333 | 28.01.2013 00:00:00 | 30.12.1899 09:00:00 |
| 184 | 1112 | 32496 | 32854 | 358 | 4 | 27 | 99 | 13,2592592592592592592 | 01.02.2013 00:00:00 | 30.12.1899 14:32:04 |
| 185 | 1112 | 32854 | 33143 | 289 | 4 | 23 | 84 | 12,5652173913043478260 | 08.02.2013 00:00:00 | 30.12.1899 06:00:00 |
| 186 | 1112 | 33143 | 33490 | 347 | 4 | 26 | 100 | 13,3461538461538461538 | 15.02.2013 00:00:00 | 30.12.1899 14:24:00 |
| 187 | 1112 | 33490 | 33849 | 359 | 4 | 27 | 104 | 13,2962962962962962962 | 22.02.2013 00:00:00 | 30.12.1899 12:00:00 |
| 188 | 1112 | 33849 | 34162 | 313 | 4 | 23 | 89 | 13,6086956521739130434 | 01.03.2013 00:00:00 | 30.12.1899 07:00:00 |
| 189 | 1112 | 34162 | 34475 | 313 | 4 | 23 | 88 | 13,6086956521739130434 | 07.03.2013 00:00:00 | 30.12.1899 07:40:00 |
| 190 | 1112 | 34475 | 34840 | 365 | 4 | 25 | 90 | 14,6000000000000000000 | 14.03.2013 00:00:00 | 30.12.1899 08:00:00 |
| 191 | 1112 | 34840 | 35160 | 320 | 4 | 28 | 104 | 11,4285714285714285714 | 20.03.2013 00:00:00 | 30.12.1899 13:00:00 |
| 192 | 1112 | 35160 | 35506 | 346 | 4 | 25 | 92 | 13,8400000000000000000 | 27.03.2013 00:00:00 | 30.12.1899 13:59:05 |
| 193 | 1112 | 35506 | 35800 | 294 | 4 | 20 | 71 | 14,7000000000000000000 | 04.04.2013 00:00:00 | 30.12.1899 07:30:00 |
| 194 | 1112 | 35800 | 36100 | 300 | 4 | 22 | 79 | 13,6363636363636363636 | 09.04.2013 00:00:00 | 30.12.1899 14:00:00 |
| 195 | 1112 | 36100 | 36432 | 332 | 3 | 23 | 80 | 14,4347826086956521739 | 16.04.2013 00:00:00 | 30.12.1899 14:57:42 |
| 196 | 1112 | 36432 | 36803 | 371 | 4 | 23 | 86 | 16,1304347826086956521 | 22.04.2013 00:00:00 | 30.12.1899 08:00:00 |
| 197 | 1112 | 36803 | 37171 | 368 | 4 | 25 | 92 | 14,7200000000000000000 | 29.04.2013 00:00:00 | 30.12.1899 07:00:00 |
| 198 | 1112 | 37171 | 37568 | 397 | 4 | 25 | 93 | 15,8800000000000000000 | 06.05.2013 00:00:00 | 30.12.1899 09:00:00 |
| 199 | 1112 | 37568 | 37908 | 340 | 4 | 22 | 85 | 15,4545454545454545454 | 13.05.2013 00:00:00 | 30.12.1899 08:00:00 |
| 200 | 1112 | 37908 | 38295 | 387 | 4 | 23 | 87 | 16,8260869565217391304 | 17.05.2013 00:00:00 | 30.12.1899 06:20:00 |
| 201 | 1112 | 38295 | 38660 | 365 | 4 | 24 | 94 | 15,2083333333333333333 | 24.05.2013 00:00:00 | 30.12.1899 07:00:00 |
| 202 | 1112 | 38660 | 39000 | 340 | 4 | 23 | 93 | 14,7826086956521739130 | 11.06.2013 00:00:00 | 30.12.1899 07:15:00 |
| 203 | 1112 | 39000 | 39342 | 342 | 4 | 23 | 93 | 14,8695652173913043478 | 17.06.2013 00:00:00 | 30.12.1899 13:00:00 |
| 204 | 1112 | 39342 | 39695 | 353 | 4 | 22 | 81 | 16,0454545454545454545 | 24.06.2013 00:00:00 | 30.12.1899 07:15:00 |
| 205 | 1112 | 39695 | 40058 | 363 | 4 | 26 | 92 | 13,9615384615384615384 | 28.06.2013 00:00:00 | 30.12.1899 13:49:07 |
| 206 | 1112 | 40058 | 40430 | 372 | 3 | 23 | 80 | 16,1739130434782608695 | 09.07.2013 00:00:00 | 30.12.1899 14:41:37 |
| 207 | 1112 | 40430 | 40795 | 365 | 4 | 25 | 93 | 14,6000000000000000000 | 16.07.2013 00:00:00 | 30.12.1899 13:31:54 |
| 208 | 1112 | 40795 | 41155 | 360 | 4 | 24 | 89 | 15,0000000000000000000 | 23.07.2013 00:00:00 | 30.12.1899 14:14:37 |
| 209 | 1112 | 41155 | 41536 | 381 | 4 | 25 | 89 | 15,2400000000000000000 | 31.07.2013 00:00:00 | 30.12.1899 13:27:51 |
| 210 | 1112 | 41536 | 41889 | 353 | 3 | 23 | 81 | 15,3478260869565217391 | 09.08.2013 00:00:00 | 30.12.1899 08:00:00 |
| 211 | 1112 | 41889 | 42304 | 415 | 3 | 26 | 90 | 15,9615384615384615384 | 16.08.2013 00:00:00 | 30.12.1899 10:30:00 |
| 212 | 1112 | 42304 | 42633 | 329 | 3 | 23 | 78 | 14,3043478260869565217 | 26.08.2013 00:00:00 | 30.12.1899 09:30:00 |
| 213 | 1112 | 42633 | 42963 | 330 | 4 | 23 | 87 | 14,3478260869565217391 | 30.08.2013 00:00:00 | 30.12.1899 08:30:00 |
| 214 | 1112 | 42963 | 43325 | 362 | 4 | 24 | 87 | 15,0833333333333333333 | 10.09.2013 00:00:00 | 30.12.1899 07:20:00 |
| 215 | 1112 | 43325 | 43683 | 358 | 4 | 24 | 83 | 14,9166666666666666666 | 16.09.2013 00:00:00 | 30.12.1899 06:00:00 |
| 216 | 1112 | 43683 | 44040 | 357 | 3 | 23 | 80 | 15,5217391304347826086 | 23.09.2013 00:00:00 | 30.12.1899 08:45:00 |
+-----+------------+-------+-------+--------------+---------------+-------------+------------+--------------------------+---------------------+---------------------+SELECT
ft.vehicle_id
, ft.prev_meter pm
, ft.meter cm
, CASE
WHEN ft.meter - ft.prev_meter < 0 THEN 0
ELSE ft.meter - ft.prev_meter
END AS driven_miles
, ft.cost_per_unit
, ft.total_units
, ft.cost_total
, CASE
WHEN ft.meter - ft.prev_meter < 0 THEN 0
ELSE (ft.meter - ft.prev_meter) / ft.total_units
END AS MPG
, ft.date_trans
, ft.time_trans
FROM (SELECT
*
, COALESCE(LAG(meter) OVER (PARTITION BY vehicle_id ORDER BY date_trans, time_trans),0) AS prev_meter
FROM @fuel_transaction
) AS ft
ORDER BY
vehicle_id
, date_trans
, time_transASKER
Thanks. I'll try it in the morning.
I didn't write the view being used. It originally had problems with the mileage calculation since it didn't account for the change of vehicles. The case statements took care of that. Now just want to get the initial meter reading to be 0.
There will still be issues since the table doesn't have an initial meter reading for when a vehicle is put into service but 0 is better than the previous car's meter reading.
mlmcc
I didn't write the view being used. It originally had problems with the mileage calculation since it didn't account for the change of vehicles. The case statements took care of that. Now just want to get the initial meter reading to be 0.
There will still be issues since the table doesn't have an initial meter reading for when a vehicle is put into service but 0 is better than the previous car's meter reading.
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
The last version using CTE works and produces what we need.
The versions with LEAD and LAG fail with error of an unknown function LEAD or LAG.
mlmcc
The last version using CTE works and produces what we need.
The versions with LEAD and LAG fail with error of an unknown function LEAD or LAG.
mlmcc