MySQL table value difference related to current date and previous date

Question

I have some tables that used to manage vehicle details. Related tables as follows:

store_item

+---------+-----------+--------+
| item_id | item_name | status |
+---------+-----------+--------+
|       1 | Diesel    |      1 |
|       2 | Petrol    |      1 |
+---------+-----------+--------+

tbl_vehicle

+------------+---------------+
| vehicle_id | registered_no |
+------------+---------------+
|          1 | LE-7476       |
|          2 | 270-0523      |
+------------+---------------+

tbl_direct_fuel

+----------------+----------+---------+------------+------------+
| direct_fuel_id | vehicle  | orderNo | issue_date | milo_meter |
+----------------+----------+---------+------------+------------+
|              1 | LE-7476  |  173072 | 2019-11-12 |    30,000  |
|              2 | LE-7476  |  173069 | 2019-11-08 |    29,600  |
|              3 | LE-7476  |  173059 | 2019-11-05 |    29,000  |
|              4 | LE-7476  |  173055 | 2019-10-08 |    25,000  |
|              5 | 270-0523 |  173068 | 2019-11-02 |    10,000  |
|              6 | 270-0523 |  173067 | 2019-10-02 |     8,500  |
+----------------+----------+---------+------------+------------+

tbl_direct_fuel_details

+------------------------+----------------+------+----------+------------+
| direct_fuel_details_id | direct_fuel_id | item | fuel_qty | fuel_price |
+------------------------+----------------+------+----------+------------+
|                    100 |              1 |    1 |       20 |        105 |
|                    101 |              2 |    1 |       15 |        105 |
|                    102 |              3 |    1 |       12 |        105 |
|                    103 |              4 |    2 |       50 |        165 |
|                    104 |              5 |    1 |       25 |        100 |
|                    105 |              6 |    2 |       18 |        165 |
+------------------------+----------------+------+----------+------------+

Desired Output

I want to get no_of_kms, vs issued fuel quantities using issue_date & milo_meter (current milo_meter - previous milo_meter). The output as follows :

+----------+-----------+
| vehicle  | no_of_kms |
+----------+-----------+
| LE-7476  |      400  |
| LE-7476  |      600  |
| LE-7476  |    4,000  |
| 270-0523 |     1500  |
+----------+-----------+

I used the following query :

select v1.registered_no as vehicle, si.item_name as fuel, df.milo_meter - df.milo_meter  as no_of_kms
from (select dfd.item, sum(dfd.fuel_qty) AS qty
      from tbl_direct_fuel df
      join tbl_direct_fuel_details dfd on df.direct_fuel_id = dfd.direct_fuel_id
      join tbl_vehicle v1 on df.vehicle = v1.vehicle_id
      where df.status = 1
      group by registered_no) dfd
join store_item si on dfd.item = si.item_id
join (select item, sum(dfd.fuel_qty) AS fuel_qty
      from tbl_direct_fuel_details
      group by item) dfd on si.item_id=dfd.item

But the above query didn't working fine. What may be going wrong ? Can anyone help me ?

Answer 1

If you are running MySQL 8.0, you can do this simply with window function lag():

select *
from (
    select
        issue_date,
        vehicle,
        milo_meter 
            - lag(milo_meter) over(partition by vehicle order by issue_date) no_of_kms 
    from tbl_direct_fuel
) t
where no_of_kms is not null
order by vehicle desc, issue_date desc

I added the issue_date to the output columns, since this seems like a sensible information to understand the results.

---

In earlier versions, I think that an inline query might do the trick:

select *
from (
    select
        issue_date,
        vehicle,
        milo_meter - (
            select max(milo_meter) 
            from tbl_direct_fuel d1
            where d1.vehicle = d.vehicle and d1.issue_date < d.issue_date
        ) no_of_kms 
    from tbl_direct_fuel d
) t
where no_of_kms is not null
order by vehicle desc, issue_date desc

This assumes that the milo_meter of a given vehicule can only increase, which seems like a reasonable assumption.

Demo on DB Fiddle

Both queries return:

issue_date | vehicle  | no_of_kms
:--------- | :------- | --------:
2019-11-12 | LE-7476  |       400
2019-11-08 | LE-7476  |       600
2019-11-05 | LE-7476  |      4000
2019-11-02 | 270-0523 |      1500