SQL & PL/SQL

1 error has occurred

Your session has timed out.

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!

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

591 views

SQL & PL/SQL

Need to alter existing query to fetch correct data.

Comments

Post Details