Hi All,
Below is my query
WITH parameters AS
(
SELECT TO\_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS startdt -- Always use 4 digits for years
, TO\_DATE ('30-JUN-2019', 'DD-MON-YYYY') AS enddt
, 'CWR' AS per\_org
FROM dual
)
, got_business_unit AS
(
SELECT p.startdt, p.enddt
, d1.emplid, d1.per\_org, d1.effdt
, CASE
WHEN b.business\_unit IS NULL
THEN 'I'
ELSE d1.hr\_status
END AS hr\_status
, b.business\_unit
FROM parameters p
JOIN ps\_cts\_job\_dtl1 d1 ON d1.per\_org = p.per\_org
AND d1.effdt \<= p.enddt
OUTER APPLY (
SELECT MAX (b.business\_unit) AS business\_unit
FROM ps\_cts\_job\_dtl d
JOIN ps\_cts\_iqn\_bu b ON b.business\_unit = d.business\_unit
WHERE d.emplid = d1.emplid
AND d.effdt = d1.effdt
) b
)
SELECT g.emplid
, GREATEST ( g.effdt
, g.startdt
) AS startdt
, LEAST ( e.enddt
, g.enddt
) AS enddt
, g.per_org
, g.hr_status
, 'Employee is active as CWR and BU is in IQN_BU setup table'
AS reason
FROM got_business_unit g
OUTER APPLY (
SELECT NVL ( MIN (gs.effdt) - 1
, DATE '9999-12-31'
) AS enddt
FROM got\_business\_unit gs
WHERE gs.emplid = g.emplid
AND gs.effdt > g.effdt
AND gs.hr\_status = 'I'
) e
WHERE hr_status = 'A'
AND e.enddt > g.startdt
ORDER BY emplid
, startdt
;
Out Put from the query is

Need only one row for employee 733930 from 01-Jun-2019 to 03-Jun-2019.
need only one row for employee 733931 from 26-Jun-2019 to 30-Jun-2019.
I want out put like below based on below tables and data.
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 CWR in between '01-Jun-2019' to '03-Jun-2019' and employee have only one row with HR_STATUS ='A' ( employee have more than one row with HR_STATUS = 'A' ) and Business_unit value present in PS_CTS_IQN_BU table.
- Employee 733930 is active CWR in between '15-Jun-2019' to '18-Jun-2019' and employee have only one row with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
- Employee 733930 is active as CWR in between '19-JUN-2019' to '25-JUN-2019' in different BU which not in IQN_BU setup table, so i want to exclude that information.
- Employee 733930 is active CWR from '26-Jun-2019' with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
- Employee 733931 is active CWR from '06-Jun-2019' to '09-Jun-2019' and, employee have only one row with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
- Employee 733931 is active CWR from '26-Jun-2019' to '30-Jun-2019' and he is having more than one row with HR_STATUS = 'A' and Business_unit value present in PS_CTS_IQN_BU table.
| EMPLID | FROM_DATE | To_DATE | PER_ORG | HR_STATUS | Reason |
| 733930 | 01-Jun-2019 | 03-Jun-2019 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 15-Jun-2019 | 18-Jun-2019 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 26-Jun-2019 | 30-Jun-2019 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 06-Jun-2019 | 09-Jun-2019 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 26-Jun-19 | 30-JUN-2019 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
Below is the requirement details.
- I want to fetch the contract employee information with HR_Status = 'A'. ( per_org = 'CWR' in PS_CTS_JOB_DTL1 table ).
- And active contract employee Business_Unit value of 'PS_CTS_JOB_DTL' table should be present in PS_CTS_IQN_BU table.
- there is a chance to have an employee multiple consecutive rows with hr_status='A'
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)
Note : Data Volume of this table is more than 50 million rows
| PS_CTS_JOB_DTL Table |
| EMPLID | BUSINESS_UNIT | EMPL_RCD | EFFDT | EFFSEQ | ACTION |
| 733930 | US410 | 0 | 27-May-2019 | 0 | ADD |
| 733930 | US410 | 0 | 02-Jun-2019 | 0 | DTA |
| 733930 | US410 | 0 | 04-Jun-2019 | 0 | TER |
| 733930 | US410 | 1 | 06-Jun-2019 | 0 | HIR |
| 733930 | US410 | 1 | 15-Jun-2019 | 0 | TER |
| 733930 | US410 | 2 | 15-Jun-2019 | 1 | ADD |
| 733930 | US608 | 2 | 19-Jun-2019 | 0 | ASC |
| 733930 | CN512 | 2 | 26-Jun-2019 | 0 | ASG |
| 733931 | US410 | 0 | 26-Apr-2019 | 0 | HIR |
| 733931 | US410 | 0 | 05-Jun-2019 | 0 | TER |
| 733931 | IN208 | 1 | 06-Jun-2019 | 0 | ADD |
| 733931 | IN208 | 1 | 10-Jun-2019 | 0 | TER |
| 733931 | IN208 | 2 | 12-Jun-2019 | 0 | HIR |
| 733931 | IN408 | 2 | 18-Jun-2019 | 0 | ASC |
| 733931 | IN408 | 2 | 19-Jun-2019 | 0 | TER |
| 733931 | IN208 | 3 | 26-Jun-2019 | 0 | ADD |
| 733931 | IN208 | 3 | 28-Jun-2019 | 0 | DTA |
PS_CTS_JOB_DTL1 Table :
Note : Data Volume of this table is more than 50 million rows
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 )
Note : employee may contains more then one row with HR_STATUS = 'A' before he is going to inactive ( HR_STSTUS = 'I' )
| EMPL_ID | EMPL_RCD | EFFDT | EFFSEQ | HR_STATUS | PER_ORG |
| 733930 | 0 | 27-May-2019 | 0 | A | CWR |
| 733930 | 0 | 02-Jun-2019 | 0 | A | CWR |
| 733930 | 0 | 04-Jun-2019 | 0 | I | CWR |
| 733930 | 1 | 06-Jun-2019 | 0 | A | EMP |
| 733930 | 1 | 15-Jun-2019 | 0 | I | EMP |
| 733930 | 2 | 15-Jun-2019 | 1 | A | CWR |
| 733930 | 2 | 19-Jun-2019 | 0 | A | CWR |
| 733930 | 2 | 26-Jun-2019 | 0 | A | CWR |
| 733931 | 0 | 26-Apr-2019 | 0 | A | EMP |
| 733931 | 0 | 05-Jun-2019 | 0 | I | EMP |
| 733931 | 1 | 06-Jun-2019 | 0 | A | CWR |
| 733931 | 1 | 10-Jun-2019 | 0 | I | CWR |
| 733931 | 2 | 12-Jun-2019 | 0 | |