Блог

Я пытаюсь написать специальный запрос для диапазона записей даты назначения по названию должности сотрудника. Эти примеры используются для таблицы назначения приложений Oracle.

Первый пример:

 AsgId  Start_Date  End_Date  Job_ID 
  1     1/1/14      6/30/14    10
  1     7/1/14      11/15/14   10
  1     11/16/14    1/10/15    20
  1     1/11/15     3/10/15    10
  1     3/11/15     3/31/15    10
  1     4/1/15      12/31/18   20
  

Я безуспешно пробовал аналитические функции, встроенные представления и другой код.

Ожидаемые результаты отчета по 3 записям диапазона дат по названию должности:

 asgid   start_date   end_date  job_title
 1       1/1/14      11/15/14     10
 1       11/16/14    1/10/15      20
 1       1/11/15     3/31/15      10
 1       4/1/15      12/31/18     20
  

Второй пример:

 EMP_ID START_DATE END_DATE JOB_TITLE 
1      1/1/14     11/15/14 10 
1      11/16/14   11/10/15 10 
1      11/11/15   12/31/15 20 
1      1/1/16     1/31/16  10 
1      2/1/16     12/31/16 10 
  

Ожидаемые результаты отчета по 3 записям диапазона дат по названию должности

 EMP_ID START_DATE END_DATE JOB_TITLE 
1      1/1/14     11/10/15 10 
1      11/11/15   12/31/15 20 
1      1/1/16     12/31/16 10
  

Это тип проблемы пробелов и островов. Предполагая, что нет пробелов или перекрытий, вы можете использовать left join и кумулятивную сумму для определения островов. Остальное — агрегирование:

 select asgid, job_id, min(start_date) as start_date,
       max(end_date) as end_date
from (select a.*,
             sum(case when aprev.asgid is null then 1 else 0 end) over (partition by a.asgid, a.job_id order by a.start_date) as grp
      from assignment a left join
           assignment aprev
           on aprev.asgid = a.asgid and
              aprev.job_id = a.job_id and
              aprev.end_date = a.start_date - 1
     ) a
group by asgid, job_id, grp
order by asgid, min(a.start_date);
  

Здесь есть db<>fiddle.