Hi All,
I need to Rationalize status if there are more than 1 status for a Employee based on Status ranking (priority )
I am trying to write a SQL query in Oracle but unable to figure out the logic
- EMP_PRSN_NO to EMP_ID relationship 1:1 or 1:M
- EMP_PRSN_NO to STAT relationship 1:1 or 1:M
- EMP_ID to STAT relationship 1:1
- PRIORITY OF STAT
1.ACTIVE
2.COMPLAINCE
3.TERMINATE
4.CANCELLED
5.NOT SET
6.UNKNOWN
7.NULL
- OUTPUT from Below Table should come like below STAT_MODIFED can be new column or new values can be in same column like in case
- ORACLE VERSION - ORACLE Database 19c Enterprise Edition
CREATE TABLE EMP_STAT
(
EMP_PRSN_NO NUMBER(5),
EMP_ID NUMBER (5),
STAT VARCHAR2(10)
)
;
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (11,11,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (12,34,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (12,36,'TERMINATE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (12,37,'CANCELLED');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (13,23,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (14,33,'TERMINATE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (15,45,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (15,56,'UNKNOWN');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (12,37,'COMPLAINCE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (15,57,'NOT SET');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (15,58,'TERMINATE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (17,45,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (18,67,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (19,76,'TERMINATE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (21,88,'');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (21,87,'CANCELLED');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (21,98,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (22,101,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (23,102,'ACTIVE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (24,103,'TERMINATE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (25,106,'TERMINATE');
INSERT INTO EMP_STAT (EMP_PRSN_NO,EMP_ID,STAT) VALUES (25,107,'COMPLAINCE');