Блог

Условие 1: если транспортное средство не вышло из строя, рассчитайте суммарные часы пробега между датой запуска транспортного средства и сегодняшней датой.

Условие 2: если транспортное средство вышло из строя, рассчитайте совокупные часы работы между датой запуска транспортного средства и датой выхода из строя

Примечание: каждое транспортное средство имеет несколько/одну дату запуска и несколько/одну/ни одной даты сбоя. поэтому рассчитайте совокупные часы/годы работы для каждого идентификатора транспортного средства

Пример таблицы SQL ниже,

вышеуказанный идентификатор транспортного средства 1 имеет 2 даты начала и 1 дату сбоя, поэтому рассчитайте годы работы между первой датой запуска и первой датой сбоя второй датой запуска до даты завершения( после второй даты запуска сбоя не произошло).

Идентификатор транспортного средства 4 имеет 2 даты начала 2 даты сбоя, поэтому рассчитайте годы работы между первой датой запуска и первой датой сбоя второй датой запуска и второй датой сбоя.

Я попытался выполнить следующий код для расчета часов работы между датой начала и датой сбоя, но получил борьбу за исключение идентификатора транспортного средства для условий ниже.

Я получаю результат в следующем формате

Above Run years calculated based on below,

Vehichle Id 1:

1-й запуск до 1-го неудачного = 2,21917

2-й запуск до даты(нет 2-го сбоя в идентификаторе транспортного средства 1) = 0,13972

таким образом, сумма лет выполнения = 2,3589.

Идентификационный номер транспортного средства 4:

1-й запуск до 1-го неудачного = 3,986

со 2-го начала до 2-й неудачной даты = 3.123

таким образом, сумма лет выполнения = 7,109.

** Требуемое заявление для сценария исключения**

Идентификационный номер транспортного средства 5

идентификатор транспортного средства 5 имеет 2 даты начала. дата сбоя отсутствует после 1-й даты начала. поэтому исключите это для расчета лет выполнения.

Идентификационный номер транспортного средства 6

идентификатор транспортного средства 6 содержит 2 даты сбоя и 1 дату запуска. 2-я дата начала отсутствует после 1-й неудачной даты. поэтому исключите это для расчета лет выполнения.

Идентификационный номер транспортного средства 7

идентификатор транспортного средства 7 содержит 3 даты начала и 1 дату сбоя. 2-я неудачная дата отсутствует после 2-й даты начала. поэтому исключите это для расчета лет выполнения.

Фактическая логика, которую я требовал, количество дат начала — количество неудачных дат равно 2 или -1 или -2, затем исключается для расчета лет выполнения.

Следующие коды я использую для расчета часов работы без исключения вышеуказанного условия

 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;
 

Вы можете назначить группу для каждого набора «запусков» и «сбоев», выполнив обратный подсчет сбоев. Затем агрегируйте для каждого «периода»:

 select vehicle_id,
       min(case when status = 'start' then date_on end) as start_date,
       coalesce(max(case when status = 'fail' then date_on end),
                getdate()
               ) as end_date,
       datediff(hours,
                min(case when status = 'start' then date_on end) as start_date,
                coalesce(max(case when status = 'fail' then date_on end),
                         getdate()
               ) as diff_hours             
from (select t.*,
             sum(case when status = 'fail' then 1 else 0 end) over (partition by vehicle_id order by date_on) as grp

      from t
     ) t
group by vehicle_id, grp;
 

Затем вы можете снова агрегировать, если хотите получить общее количество за vehicle .

Кроме того, это обеспечивает разницу в часах.