Calculate Mileage Of Vehicles By Subtracting From Vehicle Readings

I have a table in which vehicle mileage i.e the mileage of the vehicle is stored on a regular basis. The table: ---------- ID (primary key) Vehicle_id date_of_reading (datetime)

Solution 1:

Huge fat disclaimer: This comes with no warranty, but if I have understood your problem and I'm not wrong somewhere, consider the following.

Your table being

CREATETABLE `foo` (`id` INTEGER, `date` DATE, `mileage` INT);

One possible way is to first create a view with the date of last month's latest reading (which you can turn into another nested SELECT if you are so inclined):

CREATEVIEW `past_month_readings` 
AS (SELECT `id`, 
    max(`date`) AS `latestreading`
    FROM `foo` 
    WHEREMONTH(`date`) =MONTH(NOW())-1GROUPBY `id`);

Followed by

SELECT `currentmileages`.`id`, `currentmileage`-`previousmileage` FROM 
 (SELECT `foo`.`id`, `mileage` AS `currentmileage` 
  FROM `foo` 
  JOIN (SELECT `id`, MAX(`date`) AS `latestreading` FROM `foo` GROUP BY `id`) 
  AS `baz` 
  ON `foo`.`id` = `baz`.`id`
  AND `foo`.`date` = `baz`.`latestreading`
 ) 
AS `currentmileages`
JOIN
 (SELECT `foo`.`id`, `mileage` AS `previousmileage` 
  FROM `foo` 
  JOIN `past_month_readings` 
  ON `foo`.`id`=`past_month_readings`.`id` 
  AND `foo`.`date` = `past_month_readings`.`latestreading`)
AS `previousmileages`
ON
`currentmileages`.`id` = `previousmileages`.`id`

For

+------+------------+---------+|id|date|mileage|+------+------------+---------+|1|2015-06-15|1234||1|2015-07-15|1444||1|2015-07-25|2000||2|2015-06-01|100||2|2015-06-20|200||2|2015-07-20|300|+------+------------+---------+

this results in

+------+------------------------------------+
| id   | `currentmileage`-`previousmileage` |
+------+------------------------------------+
|    1 |                                766 |
|    2 |                                100 |
+------+------------------------------------+

Solution 2:

I had the same question. Looks like I solved it with the following code:

SELECT Main.vehicle_id, Main.latestreading as currentmileage, Link.latestReadin as previousmilage 
FROM (SELECT m1.*, max(l1.rowid) as LinkID
FROM `foo` as m1
JOIN `foo` as l1
ON l1.vehicle_id= m1.vehicle_id
WHERE l1.date< m1.dateGROUPBY m1.rowid
ORDERBY m1.rowid desc) as Main
LEFT JOIN (SELECT * FROM `foo`) as Link
ON Link.rowid = Main.LinkID