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. HOWTO View Tables and Indexes in an Oracle Database
3. Date and Timestamp Repository Data Types
4. Set ATG Repository Item Date or Timestamp Properties to the Current Time
5. Updating/Reading Dates