I need to find the employees that are not having event type(join(1)/retire (2)) in a location with date sequence. The combination of join and retire events in a location should occur first, before employee join another location. Please note that the employee can retire in one location, on the same date, he can join in the another location.
I have following data in the table, I need help to write a query that will return the Dataset1, DataSet2 and should not return the second DataSet3.
I have created the dataset at the following location
https://livesql.oracle.com/apex/livesql/s/gbg8ozguh66uxkxsialtdwi5h Dataset1:
Emp ID Location Date(mm/dd/yyy)Event Type CREATION_TRAN_SERIAL_NUMBER
123 111 4/3/2017 2
123 222 2/6/2017 2
123 222 1/18/2017 1
123 111 8/1/2016 1
Dataset2:
Emp ID Location Date Event Type
123 222 1/18/2017 1
123 111 8/1/2016 1
Dataset3:
Emp ID Location Date Event Type
456 111 3/10/2017 2
456 111 2/6/2017 1
456 333 2/6/2017 2
456 333 1/20/2017 1
456 222 12/14/2016 2
456 111 12/7/2016 2
456 222 12/7/2016 1
456 111 8/22/2016 1
create table T_EVENT_TMP (
EMP_ID NUMBER,
LOCATION_ID NUMBER,
EVENT_DATE DATE,
EVENT_TYPE VARCHAR2(4000),
CREATION_TRAN_SERIAL_NUMBER NUMBER
);
CREATE UNIQUE INDEX PK_T_EVENT_TMP ON T_EVENT_TMP
(EMP_ID, CREATION_TRAN_SERIAL_NUMBER);
--DataSet1
INSERT INTO T_EVENT_TMP VALUES(123, 111, TO_DATE('08/01/2016','mm/dd/yyyy'), 1, 1);
INSERT INTO T_EVENT_TMP VALUES(123, 222, TO_DATE('01/18/2017','mm/dd/yyyy'), 1, 2);
INSERT INTO T_EVENT_TMP VALUES(123, 222, TO_DATE('02/06/2017','mm/dd/yyyy'), 2, 3);
INSERT INTO T_EVENT_TMP VALUES(123, 111, TO_DATE('04/03/2017','mm/dd/yyyy'), 2, 4);
--DataSet2
INSERT INTO T_EVENT_TMP VALUES(456, 333, TO_DATE('08/01/2016','mm/dd/yyyy'), 1, 5);
INSERT INTO T_EVENT_TMP VALUES(456, 444, TO_DATE('01/18/2017','mm/dd/yyyy'), 1, 6);
--DataSet3
INSERT INTO T_EVENT_TMP VALUES(777, 111, TO_DATE('08/22/2016','mm/dd/yyyy'), 1, 7);
INSERT INTO T_EVENT_TMP VALUES(777, 222, TO_DATE('12/07/2016','mm/dd/yyyy'), 1, 8);
INSERT INTO T_EVENT_TMP VALUES(777, 111, TO_DATE('12/07/2016','mm/dd/yyyy'), 2, 9);
INSERT INTO T_EVENT_TMP VALUES(777, 222, TO_DATE('12/14/2016','mm/dd/yyyy'), 2, 10);
INSERT INTO T_EVENT_TMP VALUES(777, 333, TO_DATE('01/20/2017','mm/dd/yyyy'), 1, 11);
INSERT INTO T_EVENT_TMP VALUES(777, 333, TO_DATE('02/06/2017','mm/dd/yyyy'), 2, 12);
INSERT INTO T_EVENT_TMP VALUES(777, 111, TO_DATE('02/06/2017','mm/dd/yyyy'), 1, 13);
INSERT INTO T_EVENT_TMP VALUES(777, 111, TO_DATE('03/10/2017','mm/dd/yyyy'), 2, 14);I greatly appreciate your help.
Regards
Shiek
So, for a particular employee when you order rows by date and serial number, you expect:
- The rows to alternate between joins (type 1) and retires (type 2)
- Each retire row should have the same location as the previous join
Correct?
If so, you have a problem if:
- There are two or more joins in a row
- There are two or more retires in a row
- You have a join followed by a retire for a different location
Finding these is easy with 12c's match_recognize. Create the pattern variables for each of these. Then search for one or more occurrences of any of these patterns.
Extending the example to include a mismatched retire gives:
INSERT INTO T_EVENT VALUES(777, 888, TO_DATE('04/10/2017','mm/dd/yyyy'), 1, 15);
INSERT INTO T_EVENT VALUES(777, 999, TO_DATE('05/10/2017','mm/dd/yyyy'), 2, 16);
select *
from t_event match_recognize (
partition by emp_id
order by event_date, creation_tran_serial_number
measures
match_number() mno,
classifier() cls
all rows per match with unmatched rows
pattern ( ( join_{2,} | retire{2,} | (join_ retire_mismatch) )+ )
define
join_ as event_type = 1,
retire as event_type = 2,
retire_mismatch as event_type = 2 and prev(location_id) <> location_id
);
EMP_ID EVENT_DAT CREATION_TRAN_SERIAL_NUMBER MNO CLS LOCATION_ID EVENT_TYPE
---------- --------- --------------------------- ---------- --------------- ----------- ----------
123 01-AUG-16 1 1 JOIN_ 111 1
123 18-JAN-17 2 1 JOIN_ 222 1
123 06-FEB-17 3 1 RETIRE 222 2
123 03-APR-17 4 1 RETIRE 111 2
456 01-AUG-16 5 1 JOIN_ 333 1
456 18-JAN-17 6 1 JOIN_ 444 1
777 22-AUG-16 7 1 JOIN_ 111 1
777 07-DEC-16 8 1 JOIN_ 222 1
777 07-DEC-16 9 1 RETIRE 111 2
777 14-DEC-16 10 1 RETIRE 222 2
777 20-JAN-17 11 333 1
777 06-FEB-17 12 333 2
777 06-FEB-17 13 111 1
777 10-MAR-17 14 111 2
777 10-APR-17 15 2 JOIN_ 888 1
777 10-MAY-17 16 2 RETIRE_MISMATCH 999 2I've included the unmatched rows to help see what's going on. If you only want the failing rows, remove "with unmatched rows" from "all rows per match".
And if this the logic, how certain are you the serial numbers are in the correct order? Because sequences alone aren't enough to guarantee this...