• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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

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 - 1

Open in new window


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,


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 - 1

Open in new window


Since vehicles are purchased new that will start the previous mileage correctly.  

mlmcc
0
mlmcc
Asked:
mlmcc
  • 5
  • 4
1 Solution
 
Pawan KumarDatabase ExpertCommented:
could you please provide some sample data and the expected output.
0
 
mlmccAuthor Commented:
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
0
 
PortletPaulCommented:
What version of SQL Server do you use?

Do you have the ability to use LEAD() & LAG() ?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
mlmccAuthor Commented:
MS SQL Server 2012

I don't know about LEAD and LAG.  

mlmcc
0
 
PortletPaulCommented:
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.

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
;

Open in new window


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 |
+-----+------------+-------+-----------+------------+---------------------+---------------------+

Open in new window


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
    

Open in new window

0
 
PortletPaulCommented:
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()
0
 
PortletPaulCommented:
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 |
+-----+------------+-------+-------+--------------+---------------+-------------+------------+--------------------------+---------------------+---------------------+

Open in new window

produced by this query:
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_trans

Open in new window

see: http://rextester.com/KCAP7462
0
 
mlmccAuthor Commented:
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
0
 
PortletPaulCommented:
The CTE based approach may have been adopted prior to SQL 2012 (when lead/lag were introduced)

If you needed to stay with that CTE approach all you could include vehicle_id into the join condition
(which in my mind would be more logical)

e.g.
;
WITH CTE
AS (SELECT
                RN = ROW_NUMBER() OVER (PARTITION BY vehicle_id ORDER BY date_trans, time_trans)
              , *
        FROM @fuel_transaction)
SELECT
        [Current Row].vehicle_id
      , ISNULL([Previous Row].meter,0) pm
      , [Current Row].meter  cm
      , CASE
                WHEN [Previous Row].meter IS NULL THEN 0
                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 - 1
        AND [Previous Row].vehicle_id = [Current Row].vehicle_id
;
        

Open in new window

0
 
mlmccAuthor Commented:
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now