Вывести интервалы состояний в отпуске/не в отпуске для каждого сотрудника

Question

Таблица сотрудников EMP: EMP_ID - идентификатор сотрудника DATE_FROM - дата начала работы DATE_TO - дата окончания работы

EMP_ID  DATE_FROM            DATE_TO
1       01.01.2022           31.12.2022
2       01.01.2022           31.12.2022

Таблица отпусков VAC: VAC_ID - идентификатор отпуска EMP_ID - идентификатор сотрудника DATE_FROM - дата начала отпуска DATE_TO - дата окончания отпуска

VAC_ID EMP_ID DATE_FROM            DATE_TO
1      2      03.02.2022           10.02.2022
7      2      03.04.2022           10.04.2022

EMP_ID - идентификатор сотрудника VAC_ID - NULL, если сотрудник в этот интервал работал/идентификатор отпуска, если находился в отпуске DATE_FROM - дата начала интервала DATE_TO - дата окончания интервала

Не смог придумать простые решения на sql(только на pl/sql что-то получилось).

Как вывести интервалы состояний в отпуске/не в отпуске для каждого сотрудника?

with employees as (
  select 1 as emp_id, to_date('01.01.2022', 'dd.mm.yyyy') as date_from, to_date('31.12.2022', 'dd.mm.yyyy') as date_to from dual
  union all
  select 2 as emp_id, to_date('01.01.2022', 'dd.mm.yyyy') as date_from, to_date('31.12.2022', 'dd.mm.yyyy') as date_to from dual
)
, vacations as (
  -- первый работник в отпуск не ходил
  -- второй работник был два раза в отпуске в середине периода
  select 1 as vac_id, 2 as emp_id, to_date('03.02.2022', 'dd.mm.yyyy') as date_from, to_date('10.02.2022', 'dd.mm.yyyy') as date_to from dual
  union all
  select 7 as vac_id, 2 as emp_id, to_date('03.04.2022', 'dd.mm.yyyy') as date_from, to_date('10.04.2022', 'dd.mm.yyyy') as date_to from dual
)

Нужен такой результат:

EMP_ID VAC_ID DATE_FROM            DATE_TO
1      null   01.01.2022           31.12.2022
2      null   01.01.2022           02.02.2022
2      1      03.02.2022           10.02.2022
2      null   11.02.2022           02.04.2022
2      7      03.04.2022           10.04.2022
2      null   11.04.2022           31.12.2022

Answer 1

Задача разделения интервала на части отрезками, входящими в него.

Основная идея - размножаем строки таблицы vacations в 2 раза - сам отпуск и период после него до следующего отпуска. Если же эта строка отпуска первая для работника, добавляем еще строку от начала его работы до первого отпуска.

Посмотрите пример. Несколько расширил пример данных.

EMP_ID	DATE_FROM	DATE_TO
1	01-JAN-22	31-DEC-22
2	01-JAN-22	31-DEC-22
3	01-JAN-22	31-DEC-22
4	01-JAN-22	31-DEC-22
5	01-JAN-22	31-DEC-22
6	01-JAN-22	31-DEC-22

-- первый работник в отпуск не ходил
-- второй работник был два раза в отпуске в середине периода
-- третий работник уволился в день окончания отпуска (отпуск с увольнением)
-- четвертый брал отпуск на 1 день
-- пятый начал работу с отпуска
-- шестой пошел в отпуск сразу после отпуска

VAC_ID	EMP_ID	DATE_FROM	DATE_TO
1	2	03-FEB-22	10-FEB-22
7	2	03-APR-22	10-APR-22
8	3	03-DEC-22	31-DEC-22
9	4	01-FEB-22	01-FEB-22
10	5	01-JAN-22	03-JAN-22
11	6	01-APR-22	03-APR-22
12	6	01-MAY-22	02-MAY-22
13	6	03-MAY-22	13-MAY-22
14	6	03-JUL-22	10-JUL-22

select EMP_ID, D_FROM, D_TO, VACID, N
from(
select e.emp_id,e.date_from edate_from,e.date_to edate_to
  , v.emp_id vemp_id,v.date_from vdate_from,v.date_to vdate_to
  ,v.vac_id
  ,case when n=-1 then e.date_from
        when n=0  then coalesce(v.date_from,e.date_from)
        when n=1  then v.date_to+1
   end d_from
  ,case when n=-1 then v.date_from-1
        when n=0  then coalesce(v.date_to,e.date_to)
        when n=1  then coalesce(v.nextdt-1,e.date_to)
   end d_to
  ,case when n=-1 or n=1 then null else vac_id end vacid
  ,n
from employees e
left join (select v.* 
        ,lag(date_from)over(partition by emp_id order by date_from) prevdt
        ,lead(date_from)over(partition by emp_id order by date_from) nextdt
      from vacations v
  )v on v.emp_id=e.emp_id
  and v.date_from between e.date_from and e.date_to
left join (select -1 n from dual union all select 0 n from dual union all select 1 from dual)nn 
   on (n=0) or (n=1  )
     or(n=-1 and prevdt is null ) 
)a
where cast(d_from as date)<=cast(d_to as date)
order by emp_id,d_from,n

EMP_ID	D_FROM	D_TO	VACID	N
1	01-JAN-22	31-DEC-22	null	0
2	01-JAN-22	02-FEB-22	null	-1
2	03-FEB-22	10-FEB-22	1	0
2	11-FEB-22	02-APR-22	null	1
2	03-APR-22	10-APR-22	7	0
2	11-APR-22	31-DEC-22	null	1
3	01-JAN-22	02-DEC-22	null	-1
3	03-DEC-22	31-DEC-22	8	0
4	01-JAN-22	31-JAN-22	null	-1
4	01-FEB-22	01-FEB-22	9	0
4	02-FEB-22	31-DEC-22	null	1
5	01-JAN-22	03-JAN-22	10	0
5	04-JAN-22	31-DEC-22	null	1
6	01-JAN-22	31-MAR-22	null	-1
6	01-APR-22	03-APR-22	11	0
6	04-APR-22	30-APR-22	null	1
6	01-MAY-22	02-MAY-22	12	0
6	03-MAY-22	13-MAY-22	13	0
6	14-MAY-22	02-JUL-22	null	1
6	03-JUL-22	10-JUL-22	14	0
6	11-JUL-22	31-DEC-22	null	1

fiddle

БЛОГ НА HUSL

Вывести интервалы состояний в отпуске/не в отпуске для каждого сотрудника

Ответы (1 шт):