在peoplesoft/oracle sql中检索从终止日期起一年内重新雇用的员工

Question

我有这个表-

Empl id  Eff seq  EMP Name     Date         Action
20140531    1      abc         05-MAY-08      Hired 
20140531    1      abc         05-Jun-08      Termin 
20140531    1      abc         15-Dec-08      Rehired
20158888    1      XYZ         25-Jan-10      Hired
20158888    1      XYZ         05-MAY-10      Termin
20156666    1      BBB         12-Feb-12      Hired
20157777    1      AAA         05-MAY-13      Hired 

因此，如果我们在上述数据库上编写查询，它应该返回EMPLID-

• 20140531

由于该员工自终止之日起一年内被重新雇用。想知道怎样才能得到结果

Answer 1

假设您的表定义为

CREATE TABLE EMPLS(EMPLID       NUMBER,
                   Eff_seq      NUMBER,
                   EMP_Name     VARCHAR2(10),
                   ACTION_DATE  DATE,
                   Action       VARCHAR2(10));

，并使用问题中指定的数据填充，那么以下查询将为您提供您正在寻找的内容:

WITH REHIRED_EMPLS AS (SELECT *
                         FROM EMPLS
                         WHERE ACTION = 'Rehired'),
     TERMINATED_EMPLS AS (SELECT *
                            FROM EMPLS
                            WHERE ACTION = 'Termin')
SELECT DISTINCT r.EMPLID
  FROM REHIRED_EMPLS r
  INNER JOIN TERMINATED_EMPLS t
    ON t.EMPLID = r.EMPLID
  WHERE t.ACTION_DATE BETWEEN r.ACTION_DATE - INTERVAL '1' YEAR
                          AND r.ACTION_DATE;

SQLFiddle这里祝你好运。

<标题>编辑

根据op提供的更正数据更新查询和SQLFiddle。

Answer 2

您可以使用解析函数在过去一年内获得最新(或最早)终止日期，然后仅选择具有'Rehired'的动作的记录，这些记录具有非空last_term_date:

with emps as (
select empls.*
     , max(case action when 'Termin' then action_date end)
           over (partition by emplid
                     order by action_date
                     range between interval '1' year preceding
                               and current row)
       last_term_date
  from empls
)
select emplid from emps
 where Action = 'Rehired'
   and last_term_date is not null;

SQLFiddle

注意:我添加了一条记录，以表明一年后重新招聘的人不会被选中。

Answer 3

不需要连接的解决方案:

SQL小提琴

Oracle 11g R2 Schema Setup:

CREATE TABLE Employees ( Emplid, Effseq, EMPName, "Date", Action ) AS
          SELECT 20140531, 1, 'abc', DATE '2008-05-05', 'Hired'   FROM DUAL 
UNION ALL SELECT 20140531, 1, 'abc', DATE '2008-06-05', 'Termin'  FROM DUAL 
UNION ALL SELECT 20140531, 1, 'abc', DATE '2008-07-25', 'Interviewed' FROM DUAL 
UNION ALL SELECT 20140531, 1, 'abc', DATE '2008-12-15', 'Rehired' FROM DUAL
UNION ALL SELECT 20158888, 1, 'XYZ', DATE '2010-01-25', 'Hired'   FROM DUAL
UNION ALL SELECT 20158888, 1, 'XYZ', DATE '2010-04-05', 'Termin'  FROM DUAL
UNION ALL SELECT 20156666, 1, 'BBB', DATE '2012-02-12', 'Hired'   FROM DUAL
UNION ALL SELECT 20157777, 1, 'AAA', DATE '2013-05-05', 'Hired'   FROM DUAL;

查询1

:

WITH Previous AS (
  SELECT EmplID,
         LAG( CASE WHEN Action = 'Termin' THEN "Date" END IGNORE NULLS ) OVER ( PARTITION BY EMPName ORDER BY "Date" ) AS PrevTerminationDate,
         "Date" AS CurrentDate,
         Action
  FROM   Employees
)
SELECT EmplID
FROM   Previous
WHERE  Action  = 'Rehired'
AND    CurrentDate <= PrevTerminationDate + INTERVAL '1' YEAR

结果

:

|   EMPLID |
|----------|
| 20140531 |

查询2 -没有WITH LAG,等:

SELECT EmplID
FROM   Employees e
WHERE  Action  = 'Rehired'
AND    EXISTS(
  SELECT 'X'
  FROM   Employees x
  WHERE  x.Action =  'Termin'
  AND    e.EmplID =  x.EmplID
  AND    e."Date" >  x."Date"
  AND    e."Date" <= x."Date" + INTERVAL '1' YEAR
)

结果

:

|   EMPLID |
|----------|
| 20140531 |

Answer 4

在对我的另一个答案的评论中，您要求使用一种方法来避免子分解查询(with子句，又名公共表表达式)，并且不使用分析子句，如LAG。下面是一个简化的版本:

select distinct EMPLID
  from EMPLS E
 where Action = 'Rehired'
   and exists (select 1 from EMPLS T
                where T.EMPLID = E.EMPLID
                  and T.Action = 'Termin'
                  and t.Action_Date between e.Action_Date - interval '1' year
                                        and e.Action_Date)