主页 / 问题 / 1601670
Accepted
boogili
Asked:2024-12-05 03:51:14 +0000 UTC2024-12-05 03:51:14 +0000 UTC

显示每个员工的休假/非休假状态间隔

  • 772

员工表 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
sql

1 个回答

  1. Best Answer

    将一个区间按其中包含的段划分为多个部分的问题。

    主要思想是将假期表的行乘以 2 倍 - 假期本身以及假期之后到下一个假期的时间。如果这是该员工的第一个假期线，请添加另一条线，从他的工作开始到第一个假期。

    看一下例子。示例数据已有所扩展。

    EMP_ID DATE_FROM DATE_TO
    1 22 年 1 月 1 日 22 年 12 月 31 日
    2 22 年 1 月 1 日 22 年 12 月 31 日
    3 22 年 1 月 1 日 22 年 12 月 31 日
    4 22 年 1 月 1 日 22 年 12 月 31 日
    5 22 年 1 月 1 日 22 年 12 月 31 日
    6 22 年 1 月 1 日 22 年 12 月 31 日

    - 第一个员工没有去休假
    - 第二个员工在期间两次休假
    - 第三个员工在假期结束当天辞职（休假并解雇）
    - 第四个员工休假 1 天
    -第五个放假开始工作
    --第六个放假后就去度假

    VAC_ID EMP_ID DATE_FROM DATE_TO
    1 2 22 年 2 月 3 日 22 年 2 月 10 日
    7 2 22 年 4 月 3 日 22 年 4 月 10 日
    8 3 22 年 12 月 3 日 22 年 12 月 31 日
    9 4 22 年 2 月 1 日 22 年 2 月 1 日
    10 5 22 年 1 月 1 日 22 年 1 月 3 日
    11 6 22 年 4 月 1 日 22 年 4 月 3 日
    12 6 22 年 5 月 1 日 22 年 5 月 2 日
    13 6 22 年 5 月 3 日 22 年 5 月 13 日
    14 6 22 年 7 月 3 日 22 年 7 月 10 日 
    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 氮
    1 22 年 1 月 1 日 22 年 12 月 31 日 无效的 0
    2 22 年 1 月 1 日 22 年 2 月 2 日 无效的 -1
    2 22 年 2 月 3 日 22 年 2 月 10 日 1 0
    2 22 年 2 月 11 日 22 年 4 月 2 日 无效的 1
    2 22 年 4 月 3 日 22 年 4 月 10 日 7 0
    2 22 年 4 月 11 日 22 年 12 月 31 日 无效的 1
    3 22 年 1 月 1 日 22 年 12 月 2 日 无效的 -1
    3 22 年 12 月 3 日 22 年 12 月 31 日 8 0
    4 22 年 1 月 1 日 22 年 1 月 31 日 无效的 -1
    4 22 年 2 月 1 日 22 年 2 月 1 日 9 0
    4 22 年 2 月 2 日 22 年 12 月 31 日 无效的 1
    5 22 年 1 月 1 日 22 年 1 月 3 日 10 0
    5 22 年 1 月 4 日 22 年 12 月 31 日 无效的 1
    6 22 年 1 月 1 日 22 年 3 月 31 日 无效的 -1
    6 22 年 4 月 1 日 22 年 4 月 3 日 11 0
    6 22 年 4 月 4 日 22 年 4 月 30 日 无效的 1
    6 22 年 5 月 1 日 22 年 5 月 2 日 12 0
    6 22 年 5 月 3 日 22 年 5 月 13 日 13 0
    6 22 年 5 月 14 日 22 年 7 月 2 日 无效的 1
    6 22 年 7 月 3 日 22 年 7 月 10 日 14 0
    6 22 年 7 月 11 日 22 年 12 月 31 日 无效的 1

    小提琴

    • 0

相关问题