DBA Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Below is my "PS_CTS_JOB_DTL" table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.
EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)
EFFDT : this filed use to track when change happens.
EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )
ACTION : Will determine which action they taken on employee ( please don't consider action field to achieve output as below. because action codes may different in each time)
| JOB Table |
| EMPLID | BUSINESS_UNIT | EMPL_RCD | EFFDT | EFFSEQ | ACTION |
| 733930 | US410 | 0 | 27-May-19 | 0 | ADD |
| 733930 | US410 | 0 | 02-Jun-19 | 0 | DTA |
| 733930 | US410 | 0 | 4-Jun-19 | 0 | TER |
| 733930 | US410 | 1 | 6-Jun-19 | 0 | HIR |
| 733930 | US410 | 1 | 15-Jun-19 | 0 | TER |
| 733930 | US410 | 2 | 15-Jun-19 | 1 | ADD |
| 733930 | US608 | 2 | 19-Jun-19 | 0 | ASC |
| 733930 | CN512 | 2 | 26-Jun-19 | 0 | ASG |
| 733931 | US410 | 0 | 26-Apr-19 | 0 | HIR |
| 733931 | US410 | 0 | 5-Jun-19 | 0 | TER |
| 733931 | IN208 | 1 | 6-Jun-19 | 0 | ADD |
| 733931 | IN208 | 1 | 10-Jun-19 | 0 | TER |
| 733931 | IN208 | 2 | 12-Jun-19 | 0 | HIR |
| 733931 | IN408 | 2 | 18-Jun-19 | 0 | ASC |
| 733931 | IN408 | 2 | 19-Jun-19 | 0 | TER |
| 733931 | IN208 | 3 | 26-Jun-19 | 0 | ADD |
| 733931 | IN208 | 3 | 28-Jun-19 | 0 | DTA |
Below is my "PS_CTS_JOB_DTL1" table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.
EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)
EFFDT : this filed use to track when change happens.
EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )
HR_STATUS : To identify employee status ( A = Active, I = Inactive ).
PER_ORG : to identify employee type ( CWR = Contract, EMP = Employee )
| EMPL_ID | EMPL_RCD | EFFDT | EFFSEQ | HR_STATUS | PER_ORG |
| 733930 | 0 | 27-May-19 | 0 | A | CWR |
| 733930 | 0 | 02-Jun-19 | 0 | A | CWR |
| 733930 | 0 | 4-Jun-19 | 0 | I | CWR |
| 733930 | 1 | 6-Jun-19 | 0 | A | EMP |
| 733930 | 1 | 15-Jun-19 | 0 | I | EMP |
| 733930 | 2 | 15-Jun-19 | 1 | A | CWR |
| 733930 | 2 | 19-Jun-19 | 0 | A | CWR |
| 733930 | 2 | 26-Jun-19 | 0 | A | CWR |
| 733931 | 0 | 26-Apr-19 | 0 | A | EMP |
| 733931 | 0 | 5-Jun-19 | 0 | I | EMP |
| 733931 | 1 | 6-Jun-19 | 0 | A | CWR |
| 733931 | 1 | 10-Jun-19 | 0 | I | CWR |
| 733931 | 2 | 12-Jun-19 | 0 | A | EMP |
| 733931 | 2 | 18-Jun-19 | 0 | A | EMP |
| 733931 | 2 | 19-Jun-19 | 0 | I | EMP |
| 733931 | 3 | 26-Jun-19 | 0 | A | CWR |
| 733931 | 3 | 28-Jun-19 | 0 | A | DTA |
Below is my "PS_CTS_IQN_BU" table structure and data.
| IQN BU Setp |
| Country | Business_Unit |
| USA | US410 |
| USA | US412 |
| CAN | CN512 |
| IND | IN208 |
Based on above three tables i need to fetch below output. When i pass from date and to date in query (month start and end dates i.e like '01-JUN-19' and '30-JUN-19' ) , query should fetch employee active from active to dates as CWR and that employee Business unit should be in PS_CTS_IQN_BU table.
Required output
Note : Reason column for understanding, no need to display reason column
While fetching below out put Please map EMPLID,EMPL_RCD,EFFDT,EFFSEQ Key fields in both JOB_DTL and JOB_DTL1 ( Map Key fields is very important )
Employee 733930 is active as CWR in between '19-JUN-19' to '25-JUN-19' in different BU which not in IQN_BU setup table, so i want to exclude this information.
| EMPLID | FROM_DATE | To_DATE | PER_ORG | HR_STATUS | Reason |
| 733930 | 1-Jun-19 | 3-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 15-Jun-19 | 18-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 26-Jun-19 | 30-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 6-Jun-19 | 9-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 26-Jun-19 | 30-JUN-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
PS_CTS_JOB_DTL Table Build and Insert Scripts:
CREATE TABLE PS_CTS_JOB_DTL (EMPLID VARCHAR2(11) NOT NULL,
BUSINESS_UNIT VARCHAR2(5) NOT NULL,
EMPL_RCD INT NOT NULL,
EFFDT DATE,
EFFSEQ INT NOT NULL,
ACTION VARCHAR2(3) NOT NULL) ;
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('27-MAY-19','DD-MON-YY'),0,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-19','DD-MON-YY'),0,'DTA');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('04-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('06-JUN-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('15-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',2,to_date('15-JUN-19','DD-MON-YY'),1,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US608',2,to_date('19-JUN-19','DD-MON-YY'),0,'ASC');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','CN512',2,to_date('26-JUN-19','DD-MON-YY'),0,'ASG');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('26-APR-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('05-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('06-JUN-19','DD-MON-YY'),1,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('10-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',2,to_date('12-JUN-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('18-JUN-19','DD-MON-YY'),0,'ASC');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('19-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('26-JUN-19','DD-MON-YY'),0,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-19','DD-MON-YY'),0,'DTA');
PS_CTS_JOB_DTL1 Table Build and Insert Scripts:
CREATE TABLE PS_CTS_JOB_DTL1 (EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD INT NOT NULL,
EFFDT DATE,
EFFSEQ INT NOT NULL,
HR_STATUS VARCHAR2(1) NOT NULL,
PER_ORG VARCHAR2(3) NOT NULL) ;
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('27-MAY-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('02-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('04-JUN-19','DD-MON-YY'),0,'I','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',1,to_date('06-JUN-19','DD-MON-YY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',1,to_date('15-JUN-19','DD-MON-YY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_date('15-JUN-19','DD-MON-YY'),1,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_date('19-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_Date('26-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',0,to_date('26-APR-19','DD-MON-YY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',0,to_date('05-JUN-19','DD-MON-YY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',1,to_date('06-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',1,to_date('10-JUN-19','DD-MON-YY'),0,'I','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('12-JUN-19','DD-MON-YY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('18-JUN-19','DD-MON-YY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('19-JUN-19','DD-MON-YY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('26-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('28-JUN-19','DD-MON-YY'),0,'A','CWR');
PS_CTS_IQN_BU Table Build and Insert Scripts: ( IQN business unit setup )
CREATE TABLE PS_CTS_IQN_BU (COUNTRY VARCHAR2(3) NOT NULL,
BUSINESS_UNIT VARCHAR2(5) NOT NULL) ;
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('USA','US410');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('USA','US412');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('CAN','CN512');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('IND','IN208');