betweenGo

I have a table of data that tracks the mileage of my beloved 2005 Toyota Prius.

+----+------------+---------+---------+-------+
| id | date       | mileage | gallons | cost  |
+----+------------+---------+---------+-------+
|  1 | 2005-02-14 |     280 |   8.615 | 16.03 |
|  2 | 2005-02-27 |     480 |   4.775 |  8.59 |
|  3 | 2005-03-19 |     713 |   7.213 | 14.27 |
|  4 | 2005-04-09 |     999 |    7.86 | 16.81 |
|  5 | 2005-04-11 |    1172 |   3.174 |  6.63 |
|  6 | 2005-05-05 |    1560 |   8.889 | 18.66 |
|  7 | 2005-06-07 |    1985 |   9.815 | 20.01 |
|  8 | 2005-07-03 |    2444 |   9.868 |  21.7 |
|  9 | 2005-08-13 |    2762 |   7.728 |    20 |
| 10 | 2005-09-11 |    3271 |  10.072 | 30.11 |
| 11 | 2005-10-24 |    3646 |   8.953 | 22.19 |
| 12 | 2005-10-30 |    3959 |   6.583 | 17.11 |
| 13 | 2005-11-04 |    4184 |   4.538 | 11.25 |
| 14 | 2005-11-21 |    4631 |   8.742 |    18 |
| 15 | 2005-12-31 |    4897 |   8.511 | 18.64 |
| 16 | 2006-02-02 |    5609 |    7.83 |    18 |
| 17 | 2006-03-01 |    6064 |    3.38 |  7.16 |
| 18 | 2006-03-13 |    6400 |   7.171 |  16.2 |
| 19 | 2006-03-22 |    6605 |   5.399 | 13.17 |
| 22 | 2006-02-16 |    5944 |   7.957 |  17.5 |
| 20 | 2006-04-06 |    6974 |   7.774 | 19.43 |
| 21 | 2006-04-23 |    7316 |   7.102 | 20.45 |
+----+------------+---------+---------+-------+

I wanted a way to calculate how many miles were driven for each trip, i.e. between consecutive dates, and what was the miles per gallon (MPG) for that trip.  To do this I modified what I learned in this post, unique ID field, getting next and previous existing ID from table to create a view that does all these calculations.  The advantage of this view is that it does not matter what order I enter the data, it will always calculate correctly.

CREATE VIEW v_prius_mpg AS
  SELECT id, date, mileage, gallons, cost, mileage as trip_mileage,
         mileage / gallons AS mpg, cost / gallons AS price_per_gallon
    FROM prius_mpg
    WHERE id = 1
  UNION
  SELECT New.id AS id, New.date, New.mileage, New.gallons, New.cost,
        (New.mileage - Old.mileage) AS trip_mileage,
        (New.mileage - Old.mileage)/ New.gallons AS mpg,
        (New.cost / New.gallons) AS price_per_gallon
    FROM prius_mpg New, prius_mpg Old
    WHERE New.id > 1
      AND Old.id =
        (SELECT id
           FROM prius_mpg
           WHERE date < New.date
           ORDER BY date DESC
           LIMIT 1);

And here are the results of the view.  Note how row 20 is out of order but it still calculates correctly the trip mileage and MPG.

+----+------------+---------+---------+--------------+------------------+
| id | date       | mileage | gallons | trip_mileage | mpg              |
+----+------------+---------+---------+--------------+------------------+
|  1 | 2005-02-14 |     280 |   8.615 |          280 |  32.501450957632 |
|  2 | 2005-02-27 |     480 |   4.775 |          200 | 41.8848167539267 |
|  3 | 2005-03-19 |     713 |   7.213 |          233 | 32.3027866352419 |
|  4 | 2005-04-09 |     999 |    7.86 |          286 | 36.3867684478371 |
|  5 | 2005-04-11 |    1172 |   3.174 |          173 | 54.5053560176434 |
|  6 | 2005-05-05 |    1560 |   8.889 |          388 | 43.6494543818202 |
|  7 | 2005-06-07 |    1985 |   9.815 |          425 |  43.301069791136 |
|  8 | 2005-07-03 |    2444 |   9.868 |          459 | 46.5139845966761 |
|  9 | 2005-08-13 |    2762 |   7.728 |          318 | 41.1490683229814 |
| 10 | 2005-09-11 |    3271 |  10.072 |          509 | 50.5361397934869 |
| 11 | 2005-10-24 |    3646 |   8.953 |          375 | 41.8854015413828 |
| 12 | 2005-10-30 |    3959 |   6.583 |          313 | 47.5467112258849 |
| 13 | 2005-11-04 |    4184 |   4.538 |          225 | 49.5813133539004 |
| 14 | 2005-11-21 |    4631 |   8.742 |          447 | 51.1324639670556 |
| 15 | 2005-12-31 |    4897 |   8.511 |          266 | 31.2536717189519 |
| 16 | 2006-02-02 |    5609 |    7.83 |          712 | 90.9323116219668 |
| 17 | 2006-03-01 |    6064 |    3.38 |          120 | 35.5029585798817 |
| 18 | 2006-03-13 |    6400 |   7.171 |          336 | 46.8553897643285 |
| 19 | 2006-03-22 |    6605 |   5.399 |          205 | 37.9699944434154 |
| 20 | 2006-02-16 |    5944 |   7.957 |          335 | 42.1012944577102 |
| 21 | 2006-04-06 |    6974 |   7.774 |          369 |  47.465912014407 |
| 22 | 2006-04-23 |    7316 |   7.102 |          342 | 48.1554491692481 |
+----+------------+---------+---------+--------------+------------------+

I always wanted to appear on every row how many gallons of gas had been consumed up to that day and how much had spent on gas up to date but I could not figure out how.  The only thing I could do was determine the sums up until now.

mysql> select sum(gallons) as gallons_to_date,
       sum(cost) as cost_to_date from prius_mpg where date <= current_date;
+-----------------+--------------+
| gallons_to_date | cost_to_date |
+-----------------+--------------+
|         161.949 |       371.91 |
+-----------------+--------------+
1 row in set (0.01 sec)

Related posts:

1. Display Date in OS X Menu Bar
2. Date and Timestamp Repository Data Types
3. Set ATG Repository Item Date or Timestamp Properties to the Current Time
4. Updating/Reading Dates
5. ATG Date Tag Converter is Buggy