This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Categories

Need to alter existing query to fetch correct data.

Vemula Muni
Vemula Muni Member Posts: 57
edited Aug 13, 2019 9:41AM in SQL & PL/SQL

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

pastedImage_0.png

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.

EMPLIDFROM_DATETo_DATEPER_ORGHR_STATUSReason
73393001-Jun-201903-Jun-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393015-Jun-201918-Jun-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393026-Jun-201930-Jun-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393106-Jun-201909-Jun-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393126-Jun-1930-JUN-2019CWRAEmployee is active as CWR and BU is in IQN_BU setup table

Below is the requirement details.

  1. I want to fetch the contract employee information with HR_Status = 'A'. ( per_org = 'CWR' in PS_CTS_JOB_DTL1 table ).
  2. And active contract employee Business_Unit value of 'PS_CTS_JOB_DTL' table should be present in PS_CTS_IQN_BU table.
  3. 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
EMPLIDBUSINESS_UNITEMPL_RCDEFFDTEFFSEQACTION
733930US410027-May-20190ADD
733930US410002-Jun-20190DTA
733930US410004-Jun-20190TER
733930US410106-Jun-20190HIR
733930US410115-Jun-20190TER
733930US410215-Jun-20191ADD
733930US608219-Jun-20190ASC
733930CN512226-Jun-20190ASG
733931US410026-Apr-20190HIR
733931US410005-Jun-20190TER
733931IN208106-Jun-20190ADD
733931IN208110-Jun-20190TER
733931IN208212-Jun-20190HIR
733931IN408218-Jun-20190ASC
733931IN408219-Jun-20190TER
733931IN208326-Jun-20190ADD
733931IN208328-Jun-20190DTA

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

<td clas
EMPL_IDEMPL_RCDEFFDTEFFSEQHR_STATUSPER_ORG
733930027-May-20190ACWR
733930002-Jun-20190ACWR
733930004-Jun-20190ICWR
733930106-Jun-20190AEMP
733930115-Jun-20190IEMP
733930215-Jun-20191ACWR
733930219-Jun-20190ACWR
733930226-Jun-20190ACWR
733931026-Apr-20190AEMP
733931005-Jun-20190IEMP
733931106-Jun-20190ACWR
733931110-Jun-20190ICWR
733931212-Jun-20190
Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 7, 2019 6:15AM Answer ✓

    Hi,

    Here's one way to do it:

    WITH    parameters    AS(    SELECT  TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS pstartdt  -- Always use 4 digits for years    ,       TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') AS penddt    ,       'CWR'                                  AS per_org    FROM    dual),    got_business_unit   AS(    SELECT  p.pstartdt, p.penddt    ,       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.penddt    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    WHERE   d1.hr_status  IN ('A', 'I')  -- if necessary)SELECT    emplid,         GREATEST (sdt, pstartdt)  AS startdt,         LEAST  ( NVL (edt - 1, penddt)                 , penddt                 )                 AS enddt,         per_org,         'A'                      AS hr_status,         'Employee is active as CWR and BU is in IQN_BU setup table'                                   AS reasonFROM      got_business_unitMATCH_RECOGNIZE          (              PARTITION BY  emplid, pstartdt, penddt, per_org              ORDER BY      effdt              MEASURES      MIN (effdt)    AS sdt              ,             i.effdt        AS edt              ONE ROW PER MATCH              PATTERN       (a+ i?)              DEFINE        a  AS   hr_status  = 'A'              ,             i  AS   hr_status  = 'I'   )WHERE     NVL (edt - 1, penddt)  > pstartdtORDER BY  emplid,         sdt;

    The sub-queries are almost the same as the corresponding sub-queries you posted.

    In parameters, I changed the column names to pstartdt and penddt, to avoid confusing them with startdt and enddt elsewhere.

    In got_business_unit, I added a condition "WHERE   d1.hr_status  IN ('A', 'I')".  If hr_status is always 'A' or 'I', then you don't need it.

    · Share on TwitterShare on Facebook
«1

Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 5, 2019 9:02PM

    Hi,

    This looks almost identical to reply #12 in your earlier thread: Fetching Contract Employee information by Join the JOB_DTL,JOB_DTL1,IQN_BU Tables.

    Please don't post the same question in multiple threads.  It's confusing for people trying to answer questions, and it's confusing for you, too, since you have to look in multiple places for answers.  Mark one of these threads as "Answered" before we go any farther.

    The query you posted meets the requirements you posted.

    1. It fetches contract employee information with HR_Status = 'A'. ( per_org = 'CWR' in PS_CTS_JOB_DTL1 table ).
    2. It only returns rows where active contract employee Business_Unit value of 'PS_CTS_JOB_DTL' table is present in PS_CTS_IQN_BU table.
    3. It handles the case were an employee has multiple consecutive rows with hr_status='A'.   (It may not handle them the way you want, but you haven't said what that way is.)

    You apparently have other requirements you haven't posted yet.

    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 6, 2019 2:02AM

    Hi @Frank Kulash,

    EMPL_IDEMPL_RCDEFFDTEFFSEQHR_STATUSPER_ORG
    733930027-May-20190ACWR
    733930002-Jun-20190ACWR
    733930004-Jun-20190ICWR

    Employee 733930 is active as CWR from 27-May-2019 to 03-Jun-2019.  and he is having one extra row with HR_STATUS = 'A' and effdt 02-Jun-2019. (there is a chances employee may have more than one row with HR_STATUS = 'A' between active and inactive.). And for each effdt row employee Business_unit also available in IQN BU setup table.

    When i run the query with start date 01-JUN-2019 and end date 30-JUN-2019 Query is fetching two rows. 01-JUN-2019 to 03-JUN-2019 one row and 02-JUN-2019 to 03-JUN-2019 one row.

    instead of fetching two rows i want to fetch only one row from 01-JUN-2019 to 03-JUN-2019.  Because he is active CWR from 01-JUN-2019 to 03-JUN-2019 .

    Note :

    1. when ever there is more than one row with HR_STATUS = 'A' between start and end dates, we need to check for each row business unit is available or not in the IQN BU table. If business unit is available then we need to skip that row.

    in this case 27-MAY-2019 effdt row business unit valu is present in IQN BU table so we are fetching greatest value 01-JUN-2019 as start date.

    For 02-JUN-2019 effdt row business unit also available in IQN BU table so we need to skip this row. ( but query is fetching this row )

    2. If Business unit value is not available in IQN BU table then we need to treat row as HR_STATUS is inactive. ( This one is working fine in the Query)

    similarly for Employee 733931 also it is fetching two rows for below data.

    EMPL_IDEMPL_RCDEFFDTEFFSEQHR_STATUSPER_ORG
    733931326-Jun-20190ACWR
    733931328-Jun-20190ADTA

    Query is fetching 26-JUN-2019 to 30-JUN-2019 one row and 28-JUN-2019 to 30-JUN-2019 second row.    For 28-JUN-2019 effdt row business unit value is available in IQN BU table so  i want to fetch only one from 26-JUN-2019 to 30-JUN-2019.

    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 6, 2019 1:58PM

    Hi @Frank Kulash , Could you please help me out on this.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 6, 2019 5:24PM

    Hi,

    Sorry, I still don't understand why you want the results you posted.  The specific examples are good, but I need a general rule, such as "When there are two (or more) consecutive rows (in order by effdt) for the same emplid that all have hr_status='A', then only the first one counts.  The second (and later) consecutive 'A' rows can be ignored."

    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 7, 2019 12:35AM

    Hi @Frank Kulash, Yes correct When there are two (or more) consecutive rows (in order by effdt) for the same emplid that all have hr_status='A',  and business unit also should be present in IQN BU table for all consecutive rows then only the first one counts.  The second (and later) consecutive 'A' rows can be ignored."   { If business unit is not available in IQN BU table for consecutive rows then we need to treat that row as inactive. Currently we are checking this thing we are validating in query }

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 7, 2019 6:15AM Answer ✓

    Hi,

    Here's one way to do it:

    WITH    parameters    AS(    SELECT  TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS pstartdt  -- Always use 4 digits for years    ,       TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') AS penddt    ,       'CWR'                                  AS per_org    FROM    dual),    got_business_unit   AS(    SELECT  p.pstartdt, p.penddt    ,       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.penddt    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    WHERE   d1.hr_status  IN ('A', 'I')  -- if necessary)SELECT    emplid,         GREATEST (sdt, pstartdt)  AS startdt,         LEAST  ( NVL (edt - 1, penddt)                 , penddt                 )                 AS enddt,         per_org,         'A'                      AS hr_status,         'Employee is active as CWR and BU is in IQN_BU setup table'                                   AS reasonFROM      got_business_unitMATCH_RECOGNIZE          (              PARTITION BY  emplid, pstartdt, penddt, per_org              ORDER BY      effdt              MEASURES      MIN (effdt)    AS sdt              ,             i.effdt        AS edt              ONE ROW PER MATCH              PATTERN       (a+ i?)              DEFINE        a  AS   hr_status  = 'A'              ,             i  AS   hr_status  = 'I'   )WHERE     NVL (edt - 1, penddt)  > pstartdtORDER BY  emplid,         sdt;

    The sub-queries are almost the same as the corresponding sub-queries you posted.

    In parameters, I changed the column names to pstartdt and penddt, to avoid confusing them with startdt and enddt elsewhere.

    In got_business_unit, I added a condition "WHERE   d1.hr_status  IN ('A', 'I')".  If hr_status is always 'A' or 'I', then you don't need it.

    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 7, 2019 8:04AM

    Hi @Frank Kulash, Thanks for your support, when i execute the above SQL i am getting below error. Please help me on this.

    pastedImage_0.png

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 7, 2019 8:21AM

    HI,

    Vemula Muni wrote:Hi Frank Kulash, Thanks for your support, when i execute the above SQL i am getting below error. Please help me on this.
    pastedImage_0.png

    That doesn't look like an Oracle error message.

    IN and OUT parameters occur in PL/SQL procedures.  Nothing in this thread before this has even mentioned PL/SQL.  Are you doing something other than what is posted here?

    I don't get any error when I run the query above in SQL*Plus (version 12.2.0.1.0).  Do you?

    What front end are you using when you get the error you posted?  If it's detecting an error on line 1, then maybe that front end doesn't recognize WITH clauses (which have been part of Oracle since version 9.1, released in 2001).

    The query you posted in your original message also started with WITH.  Did it raise an error?

    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 7, 2019 8:40AM

    Hi @Frank Kulash, I am using the below version SQL developer . till i executed all your quires in this and it is accepted WITH clauses. And i am not changing the query ( Executing as it is).

    pastedImage_0.png

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 7, 2019 9:10AM

    Hi,

    Vemula Muni wrote:Hi Frank Kulash, I am using the below version SQL developer . till i executed all your quires in this and it is accepted WITH clauses. And i am not changing the query ( Executing as it is).
    pastedImage_0.png

    So you're using SQL Developer version 1.5.5?  That sounds kind of old.

    The query in reply #6 runs (and returns the results you requested) when I try it in SQL Developer 4.1.4.21.  It looks like you have a separate problem that involves SQL Developer, not SQL or PL/SQL.  Start a new thread in the SQL Developer forum.  When posting there, simplify the query as much as possible, so that it still causes the same error.  That will also help isolate exactly what couases the problem, which will help if you can't solve the SQL Developer issue and need to find a different way to run the query

    · Share on TwitterShare on Facebook