SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need to alter existing query to fetch correct data.

Vemula MuniAug 5 2019 — edited Aug 13 2019

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 | |

This post has been answered by Frank Kulash on Aug 7 2019

Jump to Answer

Processing

Added on Aug 5 2019

#sql-pl-sql

17 comments

560 views

SQL & PL/SQL

Need to alter existing query to fetch correct data.

Comments

Post Details