Solved

Complex SQL

Posted on 2016-11-16
10
71 Views
Last Modified: 2016-11-17
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
Comment
Question by:mlmcc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41889869
could you please provide some sample data and the expected output.
0
 
LVL 101

Author Comment

by:mlmcc
ID: 41889912
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41890354
What version of SQL Server do you use?

Do you have the ability to use LEAD() & LAG() ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 101

Author Comment

by:mlmcc
ID: 41890496
MS SQL Server 2012

I don't know about LEAD and LAG.  

mlmcc
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41890625
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41890636
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41890721
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
 
LVL 101

Author Comment

by:mlmcc
ID: 41890811
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41890887
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
 
LVL 101

Author Closing Comment

by:mlmcc
ID: 41891531
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question