Solved

Complex SQL

Posted on 2016-11-16
10
33 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
  • 5
  • 4
10 Comments
 
LVL 17

Expert Comment

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

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 48

Expert Comment

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

Do you have the ability to use LEAD() & LAG() ?
0
 
LVL 100

Author Comment

by:mlmcc
ID: 41890496
MS SQL Server 2012

I don't know about LEAD and LAG.  

mlmcc
0
 
LVL 48

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 48

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 48

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 100

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 48

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 100

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now