How to select rows with the latest date and calculate another field based on the row

user_2458869 0
Viewed 83

I have two tables i.e vehicle and vehicle_maintenance.

vehicle

 -----------------------------------
|   v_id    |   v_name  |   v_no    |   
 -----------------------------------
|   1       |   car1    |   car123  |
 -----------------------------------
|   2       |   car2    |   car456  |
 -----------------------------------

vehicle_maintenance

 -----------------------------------------------------------------------
|   v_main_id   |   v_id    |   v_main_date     |   v_main_remainder    |   
 -----------------------------------------------------------------------
|       1       |   1       |   2020/10/10      |       1               |
|       2       |   1       |   2020/10/20      |       2               |
|       3       |   2       |   2020/10/04      |       365             |
|       4       |   2       |   2020/10/15      |       5               |
 -----------------------------------------------------------------------

I want to get each car maintenance details i.e car2 maintenance date is 2020/10/15 and i want to check next maintenance date based on v_main_remainder field. That means next maintenance date will be 2020/10/20 ( add 5 day to the maintenance date). I want to also calculate the no of days left from next maintenance date. Suppose today is 2020/10/10 then it will show 10 days left.

Here is my query

SELECT
    v.v_id, 
    v.v_name, 
    v.v_no, 
    max(vm.v_main_date) as renewal_date,
    datediff(
        DATE_ADD(
            max(vm.v_main_date), INTERVAL +vm.v_main_remainder day
        ), 
        now()
    ) as day_left
    
FROM    vehicle as v, vehicle_maintenance as vm
GROUP BY v.v_id

But the problem is vm.v_main_remainder in date_add function taken from first row.

Here is the result

 -----------------------------------------------------------------------
|   v_id    |   v_name  |   v_no    |   renewal_date    |   day_left    |
 -----------------------------------------------------------------------
|   1       |   car1    |   car123  |   2020/10/20      |       11      |
 -----------------------------------------------------------------------
|   2       |   car2    |   car456  |   2020/10/15      |       370     |
 -----------------------------------------------------------------------
greatest-n-per-group inner-join datetime mysql sql
1 Answers

As a starter, your query is obviously missing a join condition between the two tables, so that's a cartesian product. This type of problem is much easier to spot when using explicit joins.

Then: you want to filter on the latest maintenance record per car, so aggregation is not appropriate.

One option uses window functions, available in MySQL 8.0:

select v.v_id, v.v_name, v.v_no, vm.v_main_date as renewal_date,
    datediff(vm.v_main_date + interval vm.v_main_remainder day, current_date) as day_left
from vehicle as v
inner join (
    select vm.*, row_number() over(partition by v_id order by v_main_date desc) rn
    from vehicle_maintenance
) as vm on vm.v_id = v.v_id
where vm.rn = 1

Note that I changed now() to current_date, so datediff() works consistently on dates rather than datetimes.

user_106767160
Related