Retrieving the employees Rehired within a year from their termination date in peoplesoft/oracle sql?

Question

I have this table-

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 

So if we write the query on above database, it should return the EMPLID-

• 20140531

As this employee is rehired within a year from his termination date. Wondering how we can get the results.

Answer 1

In a comment on my other answer you requested a method that avoids subfactored queries (the with clause, a.k.a. common table expressions) and which does not use analytic clauses such as LAG. So here's a simplified version:

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)

with the SQLFiddle to boot.

Answer 2

A solution that does not require joins:

SQL Fiddle

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;

Query 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

Results:

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

Query 2 - No WITH, LAG, etc:

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
)

Results:

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

Answer 3

You can use an analytic function get the latest (or earliest) termination date in within the past year then only select those records with action'Rehired' that have a non null 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;

Here's the SQLFiddle

Note: I added one record to show that rehires after one year don't get selected.

Answer 4

Assuming that your table is defined as

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

and is populated with data as specified in your question then the following query will get you what you're looking for:

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 here

Best of luck.

EDIT

Updated query and SQLFiddle based on corrected data provided by OP.