Как сформировать отчёт с записями чей интервал не попадет в условие отбора?

Question

[likilix]

Есть таблица с статусами и мне нужно рассчитать время работы по этим данным, например, таблица выглядит сложнее, но для простоты я ее упростил, оставив прежний смысл

create table statuses (
  id number not null,
  transfer date not null
);

Данные таблицы 

id transfer    equipment
1  03.08.2024  1
2  01.10.2024  2
3  03.10.2024  2
4  14.10.2024  3

with 
  statuses as (
    
    select 1 as id, to_date('03.08.2024', 'dd.mm.yyyy') as transfer, 1 as equipment from dual
    
     union all
    
    select 2 as id, to_date('01.10.2024', 'dd.mm.yyyy') as transfer, 2 as equipment from dual
    
     union all
    
    select 3 as id, to_date('03.10.2024', 'dd.mm.yyyy') as transfer, 2 as equipment from dual
    
     union all
    
    select 4 as id, to_date('14.10.2024', 'dd.mm.yyyy') as transfer, 3 as equipment from dual
    
   ), 
  t_report as (
     select t.id
               ,t.transfer as dt_start
               ,lead(t.transfer, 1, sysdate) over(partition by t.equipment order by t.transfer) as dt_end 
               ,t.equipment
       from statuses t
)

  select t.equipment, t.dt_start, t.dt_end, round((t.dt_end - t.dt_start) * 24, 1) as hour 
    from t_report  t 
  where t.dt_start between to_date('01.10.2024', 'dd.mm.yyyy') and to_date('14.10.2024', 'dd.mm.yyyy')

А вот в чем проблема:
статус transfer - это начала работы оборудования, время которого будет постоянно переходить на новые сутки, начиная
от 03.08.2024..04.08.2024..05.08.2024, без добавления записи в таблицу, вплоть до сегодняшнего дня,
т.е мне нужно как-то это хвост (id = 1) добавить в мой запрос to_date('01.10.2024') and to_date('14.10.2024')
и взять время не sysdate - 03.08.2024, а от to_date('01.10.2024') - to_date('14.10.2024')

Comments

[mletov]

Т е оборудование работает без остановки?
Так может просто оставить минимальную дату по каждому оборудованию и от нее считать разницу?

SELECT t.equipment, MIN(t.transfer) as minDate
       FROM statuses AS t
       GROUP BY t.equipment

Или вторая запись - это дата окончания работы оборудования?

[Akina]

например, таблица выглядит сложнее, но для простоты я ее упростил, оставив прежний смысл

Перестарался. В данных есть поле equipment, отсутствующее в структуре.

Выложите НОРМАЛЬНЫЙ пример. Корректные CREATE TABLE + INSERT INTO, с подробным пояснением, что хранится в каждом поле и какой смысл несёт. Внешние данные / критерии для формирования конечного результата. Собственно сам конечный результат - на показанных данных для показанных критериев, с подробным пояснением.

И обязательно выложите собственно формулировку задачи.

[likilix]

Akina, create table statuses (
id number not null,
transfer date not null,
equipment number,
mode_id number
);

Оборудование работает постоянно, но я считаю только определенные mode_id оборудования. Необходимо подсчитать количество часов работы оборудования в разных режимах

ОБОРУДОВАНИЕ РЕЖИМ(РЕЗЕРВ) РЕЖИМ(СБРОС)
Л-100 10ч 2ч
С-150 2ч 10ч

[likilix]

likilix, но это статусы в текущих сутках, а мне нужно подсчитать начиная от статуса от пред. суток т.е. за 03.08

[mletov]

likilix,

1) оконной функцией row_number() over() проставьте порядок в рамках каждого оборудования.

Вынесите в темпоралку или CTE.
И заджоинбте эту CTE саму с собой по оборудованию и rn/rn+1

2) Считайте разницу в часах между rn и rn+1 по каждому оборудовнию

Для последней даты по каждому оборудованию смотрите, если джоин отдаёт null и нету rn+1, считайте разницу с текущей датой

Ну а потом получившееся сгруппируйте по оборудованию, режиму и SUM по количеству часов

Answer 1

[yvm]

Думаю тут помогут window function