Oracle SQL — нужна помощь в преобразовании одного значения даты EFF_DT в диапазон дат START_EFF_DT и END_EFF_DT с уникальными критериями

В Oracle 12.1 и более поздних версиях вы можете использовать match_recognize для самых разных задач сопоставления с образцом.

Данные испытаний:

create table time_labor (eeid number, work_dt date, paycode varchar2(10));

alter session set nls_date_format = 'yyyy/mm/dd';

insert into time_labor (eeid, work_dt, paycode)
    select 1111, to_date('2022/03/01'), 'UNPAID'   from dual union all
    select 1111, to_date('2022/03/02'), 'REG_WORK' from dual union all
    select 1111, to_date('2022/03/03'), 'REG_WORK' from dual union all
    select 1111, to_date('2022/03/04'), 'UNPAID'   from dual union all
    select 1111, to_date('2022/03/05'), 'UNPAID'   from dual union all
    select 1111, to_date('2022/03/06'), 'SICK'     from dual union all
    select 1111, to_date('2022/03/07'), 'SICK'     from dual union all
    select 1111, to_date('2022/03/08'), 'UNPAID'   from dual union all
    select 1111, to_date('2022/03/09'), 'UNPAID'   from dual union all
    select 1111, to_date('2022/03/10'), 'UNPAID'   from dual
;

commit;

Запрос и вывод:

select eeid, start_eff_dt, end_eff_dt
from   time_labor
match_recognize(
  partition by eeid
  order     by work_dt
  measures  first(work_dt) as start_eff_dt,
            last (work_dt) as   end_eff_dt
  pattern   ( unpaid+ )
  define    unpaid as paycode = 'UNPAID'
);

EEID  START_EFF_DT  END_EFF_DT
----  ------------  ------------
1111  2022/03/01    2022/03/01
1111  2022/03/04    2022/03/05
1111  2022/03/08    2022/03/10

Механизм match_recognize разделяет входные строки и упорядочивает их в каждом разделе в соответствии с предложениями partition by и order by. Он ищет совпадения шаблона unpaid+, что означает одну или несколько последовательных строк, классифицированных как unpaid, где эта классификация определена в предложении define. (Здесь unpaid — это просто мой выбор имени для этого классификатора; это кажется разумным выбором, но его можно назвать любым другим допустимым идентификатором, например x или abc_2902). match_recognize по умолчанию возвращает одну строку на совпадение с дополнительными вычисляемыми выражениями из предложения measures.

Для пользователей более старых версий Oracle Database (до версии 12.1), которые не могут использовать предложение match_recognize, вот решение, использующее только аналитические функции и очень хороший метод выявления пробелов и островов, известный как «фиксированные различия» или «табибито». метод "сан". Хитрость заключается в создании дополнительных выражений группировки, помеченных grp, во вспомогательном подзапросе (который для простоты чтения я выделил в предложении with).

with
  prep as (
    select eeid, work_dt, paycode,
           row_number() over (partition by eeid          order by work_dt) 
         - row_number() over (partition by eeid, paycode order by work_dt) 
           as grp
    from   time_labor
  )
select eeid, min(work_dt) as start_eff_dt, max(work_dt) as end_eff_dt
from   prep
group  by eeid, paycode, grp
having paycode = 'UNPAID'
order  by eeid, start_eff_dt
;

Образцы данных (для тестирования) и результаты этого запроса можно найти в другом ответе, в котором используется match_recognize (только версии Oracle >= 12.1)