Define vehicle current condition based on available latest date in SQL server

Question

I have set of vehicle information in SQL table with their Start date and Failed date.

I need to find the vehicle current condition is Running or not.

Example Table as follow,

Vehicle_ID	status	date_on
1	Start	2018-05-23
1	Trouble	2019-05-23
1	Parked	2020-06-23
1	Start	2021-06-15
1	Failed	2020-08-10
2	Start	2019-06-23
3	Start	2010-04-20
3	Parked	2011-04-20
3	Failed	2012-05-10
4	Start	2011-01-20
4	Failed	2015-01-14
4	Start	2016-02-25

Above table says,Vehicle ID 1 latest date_on value is 2021-06-15 and their status is start, so it means Vehicle is still running.

Vehicle ID 2 has only one record and that latest date_on value is 2019-06-23 and their status is start, so it means Vehicle is still running.

Vehicle ID 3 latest date_on value is '2010-05-10' and their status is Failed, so it means Vehicle is stopped.

Required Output

VehicleID	Running?
1	Yes
2	Yes
3	No
4	Yes

Note: In status column has more than 5 different state and dates but we consider only Start and Failed Status

Answer 1

select Vehicle_ID, case when status = 'Start' then 'YES' else 'NO' end as 'Running?'
from Yourtable y1
where date_on = ( 
                 select max(date_on) 
                 from Yourtable 
                 where Vehicle_ID = y1.Vehicle_ID  
                 group by Vehicle_ID)

Answer 2

You can try the below

with cte as
(
select Vehicle_ID,status,
row_number() over(partition by Vehicle_ID order by date_on desc) as rn
from tablename
)

select Vehicle_ID, case when status='Start' then 'Yes' else 'No' end as Running
from cte 
where rn=1

Answer 3

You can use aggregation with conditional logic:

select vehicle_id,
       (case when max(date_on) = max(case when status = 'Start' then date_on end)
             then 'Yes' else 'No'
        end) as is_running
from t
group by vehicle_id;

This checks if the last date for the vehicle is the last date for "Start".