Condition 1: if vehicle not failed, calculate cumulative run hours between vehicle start date to today's date.
Condition 2: if vehicle failed, calculate cumulative run hours between vehicle start date to failed date
Note: each vehicle has multiple/single start date and multiple/single/none failed date. so calculate cumulative run hours/years for each vehicle ID
Example SQL table below,
above vehicle ID 1 has 2 start date and 1 failed date, so calculate run years between first start date to first failed date + second start date to till date( no failure happened after second start date).
Vehicle ID 4 has 2 start date 2 failed date, so calculate run years between first start date to first failed date + second start date to second failed date.
I tried following code for calculate run hours between start date to failed date but getting struggle for excluding vehicle ID for below condition.
I am getting result as below format
Above Run years calculated based on below,
Vehichle Id 1:
1st start to 1st failed = 2.21917
2nd start to till date(no 2nd failed in vehicle id 1) = 0.13972
so sum run years = 2.3589.
Vehichle Id 4:
1st start to 1st failed = 3.986
2nd start to 2nd failed date = 3.123
so sum run years = 7.109.
** Required statement for exclude scenario**
Vehicle Id 5
vehicle Id 5 has 2 start date. failed date is missing after 1st start date. so exclude this for run years calculation.
Vehicle Id 6
vehicle Id 6 has 2 Failed date and 1 start date. 2nd start date is missing after 1st failed date. so exclude this for run years calculation.
Vehicle Id 7
vehicle Id 7 has 3 Start date and 1 Failed date. 2nd failed date is missing after 2nd start date. so exclude this for run years calculation.
Actual logic i required, count of start date - count of failed date is 2 or -1 or -2 then exclude for run years calculation.
Following codes are I used to calculate run hours without exclude the above condition
select s.vehicle_id, sum(datediff(day, date_on, isnull(m.min_dt, getdate()))/365.0) run_years from #samples s cross apply ( select min(date_on) min_dt from #samples ss where s.vehicle_id = ss.vehicle_id and s.date_on < ss.date_on and ss.stat = 'Failed' ) m where s.stat = 'Start' group by s.vehicle_id order by s.vehicle_id;
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT s.vehicle_id, sum(datediff(day, date_on, isnull(m.min_dt, getdate())) / 365.0) run_years FROM #samples s CROSS APPLY (SELECT min(ss.date_on) min_dt FROM #samples ss WHERE s.vehicle_id = ss.vehicle_id AND s.date_on < ss.date_on AND ss.stat = 'Failed') m WHERE s.stat = 'Start' GROUP BY s.vehicle_id ORDER BY s.vehicle_id