14 Replies Latest reply on Aug 14, 2019 4:11 PM by mathguy

    Fetching Data Effective date wise by joining three tables

    Vemula Muni

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

       

      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-20190AEMP
      733931218-Jun-20190AEMP
      733931219-Jun-20190IEMP
      733931326-Jun-20190ACWR
      733931328-Jun-20190ADTA

       

      Below is my "PS_CTS_IQN_BU"  table structure and data.

       

      IQN BU Setp
      CountryBusiness_Unit
      USAUS410
      USAUS412
      CANCN512
      INDIN208

       

       

      Required Output based on above data :

       

      When i pass the start date as TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') and end date as  TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') Query should fetch the below data.

       

      1. When ever employee is active as CWR and business unit value is available in IQN_BU table We need to fetch that employee information.
      2. In below output first row from date and to date will be '01-JUN-2019' because there is a row in JOB_DTL table with effective date '02-JUN-2019'.
      3. in second row employee active as CWR with new row 02-JUN-2019 to 03-JUN-2019  because he is inactive on 04-JUN-2019.
      4. When there is consecutive rows with HR_STATUS ='A' then we need to break from date and to dates for each row.

       

      EMPLIDFROM_DATETo_DATEPER_ORGHR_STATUSBusiness_UnitActionEFFDTReason
      73393001-Jun-201901-Jun-2019CWRAUS410ADD27-May-2019Employee active as CWR
      73393002-Jun-201903-Jun-2019CWRAUS410DTA02-Jun-2019Employee active as CWR
      73393015-Jun-201918-Jun-2019CWRAUS410ADD15-Jun-2019Employee active as CWR
      73393026-Jun-201930-Jun-2019CWRACN512ASG26-Jun-2019Employee active as CWR
      73393106-Jun-201909-Jun-2019CWRAIN208ADD06-Jun-2019Employee active as CWR
      73393126-Jun-201927-Jun-2019CWRAIN208ADD26-Jun-2019Employee active as CWR
      73393128-Jun-201930-Jun-2019CWRAIN208LOC28-Jun-2019Employee active as CWR

       

       

       

       

       

      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-2019','DD-MON-YYYY'),0,'ADD');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-2019','DD-MON-YYYY'),0,'DTA');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('04-JUN-2019','DD-MON-YYYY'),0,'TER');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('06-JUN-2019','DD-MON-YYYY'),0,'HIR');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('15-JUN-2019','DD-MON-YYYY'),0,'TER');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',2,to_date('15-JUN-2019','DD-MON-YYYY'),1,'ADD');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US608',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'ASC');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','CN512',2,to_date('26-JUN-2019','DD-MON-YYYY'),0,'ASG');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('26-APR-2019','DD-MON-YYYY'),0,'HIR');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('05-JUN-2019','DD-MON-YYYY'),0,'TER');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('06-JUN-2019','DD-MON-YYYY'),0,'ADD');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('10-JUN-2019','DD-MON-YYYY'),0,'TER');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',2,to_date('12-JUN-2019','DD-MON-YYYY'),0,'HIR');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('18-JUN-2019','DD-MON-YYYY'),0,'ASC');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'TER');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('26-JUN-2019','DD-MON-YYYY'),0,'ADD');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-2019','DD-MON-YYYY'),0,'DTA');
      Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-2019','DD-MON-YYYY'),1,'LOC');  
      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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),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-2019','DD-MON-YYYY'),1,'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');
      

       

      Message was edited by: Vemula Muni

        • 1. Re: Fetching Data Effective date wise by joining three tables
          mathguy

          Are the (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) tuples exactly the same in both job detail tables? Are they (or could they be) primary key in each table, and also foreign key to the other table?

           

          That is the case in your sample data, but is it true in real life?

           

          One might ask, if so, then why do you have two tables instead of one... but the question should be addressed to PeopleSoft, meaning to Oracle, not to you or your organization.

          • 2. Re: Fetching Data Effective date wise by joining three tables
            Vemula Muni

            Hi mathguy, EMPLID,EMPL_RCD,EFFDT,EFFSEQ are the primary keys in both tables, and data is exactly same for those fields.  PS_CTS_JOB_DTL table will holds the business unit value but not available in PS_CTS_JOB_DTL1.

             

            HR_STATUS, PER_ORG available in PS_CTS_JOB_DTL1 but not available in PS_CTS_JOB_DTL . So i need to join all three tables to get the output.

            • 3. Re: Fetching Data Effective date wise by joining three tables
              Frank Kulash

              Hi,

               

              This looks a lot like your earlier thread, Need to alter existing query to fetch correct data.

              In that problem, you wanted one row of output for each group of consecutive rows with hr_status='A'.

              In this problem, you want a row of output for each row with hr_status-'A', not a group of rows.  That's actually easier.

              Here's one way:

              WITH    parameters    AS

              (

                  SELECT  TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS pstartdt

                  ,       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, b.action

                  ,       LEAD ( d1.effdt

                               , 1

                               , DATE '9999-12-31'

                               ) OVER ( PARTITION BY  d1.emplid

                                        ORDER BY      d1.effdt

                                      ) - 1  AS next_effdt

                  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 (bu.business_unit)  AS business_unit

                    ,       MIN (d.action)          AS action

                                  FROM    ps_cts_job_dtl  d

                                  JOIN    ps_cts_iqn_bu   bu   ON  bu.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   (effdt, pstartdt)      AS startdt

              ,         LEAST (next_effdt, penddt)        AS enddt

              ,         per_org

              ,         hr_status

              ,         action

              ,         business_unit

              ,         effdt

              ,         'Employee active as ' || per_org  AS reason

              FROM      got_business_unit

              WHERE     hr_status   = 'A'

              AND       next_effdt >= pstartdt

              ORDER BY  emplid, effdt

              ;

              You'll notice this starts off very much like the solution in reply #6 of the earlier thread.

               

              TO_DATE is the name of a built-in function, so it's a very poor name for a column.  I called the column ENDDT.

              FROM_DATE is okay for a column name, but I changed it to STARTDT to be consistent with ENDDT.

              • 4. Re: Fetching Data Effective date wise by joining three tables
                mathguy

                Here is what I came up with.

                 

                A few notes:

                 

                • TO_DATE is the name of an Oracle function; you should not use it as a column name. In some cases that may cause syntax violations, and it's a bad idea regardless. I use TO_DT instead, and then for consistency I use FROM_DT also. (Note that you are already using EFFDT; I like the underscore to make column names more readable).
                • I didn't add your REASON column; it's quite easy to add it, in the outer SELECT clause, but I don't see its purpose (since it has a single, constant, hard-coded value anyway).
                • I also don't see the point of having columns PER_ORG and HR_STATUS in the output, since your problem statement requires that they be 'CWR' and 'A' anyway. I left them there but I am not sure they should be there. Easy to remove if desired.
                • I believe the query deals correctly with employees who started with the company after 1 June, or were terminated before 30 June, but you'll have to check that on more data than you provided.
                • Since you don't pull the country from the last table, and instead you are just looking to see if the business unit is present in that table, I used a semi-join instead of a join; that's more efficient. Of course, if you must add a COUNTRY column, you can change that to an actual (inner) join.
                • I collected the report start and end dates in the first subquery in the WITH clause. I hard-coded them; you can replace that with bind variables, or whatever else you may use to give those inputs. That's the only place to make those changes; the rest of the query uses the values from the subquery, and it doesn't care how those values are given there.
                • In the PATTERN clause of MATCH_RECOGNIZE, you will see   a{0,1}   The more common way to write that is    a?   and you can try it that way; it will not work that way in SQL Developer, which I use for most of my work, for reasons that have nothing to do with Oracle database. (SQL Developer interprets the question mark there as a placeholder for a variable; this makes SQL Developer less than 100% compatible with Oracle SQL, which is a shame...)
                • If the query is too slow, it can probably be made more efficient. One thing that comes to mind is that in the MATCH_RECOGNIZE subquery we can first throw out the rows where the effective date is greater than the report end date; alas, that will likely throw out relatively few rows. It would help a lot more if we could throw out rows with effective date before the report start date, but we can't; we need information from the last such row for each employee. Still, this CAN be done (carefully) and save time, at the cost of a more complicated query. Only worth doing if execution time is not reasonable.

                 

                 

                Here it is:

                 

                with

                  report_dates (start_dt, end_dt) as (

                    select date '2019-06-01', date '2019-06-30' from dual

                  )

                , prep (emplid, empl_rcd, from_dt, to_dt, per_org, hr_status, effdt, effseq) as (

                    select emplid, empl_rcd, from_dt, to_dt, per_org, hr_status, effdt, effseq

                    from   PS_CTS_JOB_DTL1 cross join report_dates

                    match_recognize(

                      partition by emplid

                      order     by effdt, effseq

                      measures  greatest(start_dt, effdt) as from_dt, nvl(next(effdt) - 1, end_dt) as to_dt

                      all rows per match

                      pattern   (a{0,1} b c*)

                      define    a as effdt <  start_dt, b as effdt >= start_dt, c as effdt <  end_dt + 1

                    )

                    where  hr_status = 'A' and per_org = 'CWR'

                )

                select emplid, from_dt, to_dt, per_org, hr_status, business_unit, action, effdt

                from   prep inner join PS_CTS_JOB_DTL using(emplid, empl_rcd, effdt, effseq)

                where  business_unit in (select business_unit from PS_CTS_IQN_BU)

                ;

                 

                 

                EMPLID  FROM_DT      TO_DT        PER_ORG  HR_STATUS  BUSINESS_UNIT  ACTION  EFFDT

                ------  -----------  -----------  -------  ---------  -------------  ------  -----------

                733930  01-Jun-2019  01-Jun-2019  CWR      A          US410          ADD     27-May-2019

                733930  02-Jun-2019  03-Jun-2019  CWR      A          US410          DTA     02-Jun-2019

                733930  15-Jun-2019  18-Jun-2019  CWR      A          US410          ADD     15-Jun-2019

                733930  26-Jun-2019  30-Jun-2019  CWR      A          CN512          ASG     26-Jun-2019

                733931  26-Jun-2019  27-Jun-2019  CWR      A          IN208          ADD     26-Jun-2019

                733931  28-Jun-2019  30-Jun-2019  CWR      A          IN208          DTA     28-Jun-2019

                • 5. Re: Fetching Data Effective date wise by joining three tables
                  mathguy

                  Hmm...

                   

                  I just noticed that my output is different from yours - I am missing a row.

                   

                  Then I found out why.  You said:

                   

                  EMPLID,EMPL_RCD,EFFDT,EFFSEQ are the primary keys in both tables, and data is exactly same for those fields.

                   

                   

                  Perhaps that is true in your production data (I assume it is), but it is not true in the data you provided for testing.

                   

                  Namely, in the DTL table (the first one), for employee 733931, there is only one row for date 06-Jun-2019; however, the EFFSEQ number for that row is 1, instead of 0 as it should be. In the DTL1 table, the corresponding row has EFFSEQ = 0, as it should be.  This mismatch causes the join of the two tables to miss that row - and then it's missing from the results, too. If you fix the data in table DTL, my query will produce the output you show in your original post.

                   

                  Curiously, Mr. Kulash's answer is not missing that row. I assume that means (I didn't check his code, I just ran it as written) that in his join of the two tables, he is not using the EFFSEQ column at all. I assume in production this may cause serious troubles, if / when you do have multiple rows for the same employee and same date in the DTL table. This is easy to test: add INSERT statements to add rows to both tables, so that there are multiple rows on an effective date that is actually going to be in the output. You will see the kind of trouble I mean.

                  • 6. Re: Fetching Data Effective date wise by joining three tables
                    Vemula Muni

                    Hi mathguy, Thanks for your support, Query is not fetching the below row for employee 733931.

                     

                    73393106-Jun-201909-Jun-2019CWRAIN208ADD06-Jun-2019

                    Could you please check.

                    • 7. Re: Fetching Data Effective date wise by joining three tables
                      mathguy

                      Vemula Muni wrote:

                       

                      Hi mathguy, Thanks for your support, Query is not fetching the below row for employee 733931.

                       

                      73393106-Jun-201909-Jun-2019CWRAIN208ADD06-Jun-2019

                      Could you please check.

                       

                       

                      I answered that question even before you asked it. Look at Reply 5, above your question about the missing row.

                      • 8. Re: Fetching Data Effective date wise by joining three tables
                        Vemula Muni

                        Hi Frank Kulash,  Could you please let me know the significance of usage  MAX (bu.business_unit), MIN (d.action) FROM    ps_cts_job_dtl  d.

                         

                        Actually  PS_CTS_JOB_DTL table holds few more extra columns in my application. ( Action_DT, Location, Holiday_Schedule_code ) and i need to select those in out put. 

                        • 9. Re: Fetching Data Effective date wise by joining three tables
                          Vemula Muni

                          Hi mathguy, Could you please clarify how query will work for below scenario. When employee have more than one transaction in same day (more than one row with same effdt) i need to consider only the max effective sequence row.

                          Note : Below example data is not available in my insert scripts, when i execute that query in my application i found below issue.

                          insert scripts to replicate below issue :

                          Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-2019','DD-MON-YYYY'),1,'LOC');
                          Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('28-JUN-2019','DD-MON-YYYY'),1,'A','CWR');
                          

                           

                          In above data employee have two transactions on 06-APR-06. in that case query should select only second row (need to fetch only max effseq row for each effdt row ). in out put query should display from date as 06-APR-2006 and to date will be 31-DEC-2007.

                           

                          But query is fetching two rows. in below data first row is incorrect, so i need to exclude that row

                          • 10. Re: Fetching Data Effective date wise by joining three tables
                            Frank Kulash

                            Hi,

                            Vemula Muni wrote:

                             

                            Hi Frank Kulash, Could you please let me know the significance of usage MAX (bu.business_unit), MIN (d.action) FROM ps_cts_job_dtl d.

                             

                            Actually PS_CTS_JOB_DTL table holds few more extra columns in my application. ( Action_DT, Location, Holiday_Schedule_code ) and i need to select those in out put.

                            When a row is being displayed from the PS_CTS_JOB_DTL1 table, and it is related to two (or more) rows in the PS_CTS_JOB_DTL table, how do you decide which ACTION (and ACTION_DT, and other columns) to display?

                            When a row from the PS_CTS_JOB_DTL1 table is related to two (or more) rows in the PS_CTS_IQN_BU table, how do you decide which BUSINESS_UNIT to display?

                            • 11. Re: Fetching Data Effective date wise by joining three tables
                              mathguy

                              Hmm...

                               

                              Yes, I did think about that possibility (and the invalid rows that could appear in the output), but then as I was working on the solution I forgot about it.

                               

                              You ask "how it would work" - but you already know the answer.

                               

                              How to fix it - that's an easy question too. Add one more line of code at the very end of the query, in the WHERE clause of the top-level select:

                               

                              with

                                report_dates (start_dt, end_dt) as (

                                  select date '2019-06-01', date '2019-06-30' from dual

                                )

                              , prep (emplid, empl_rcd, from_dt, to_dt, per_org, hr_status, effdt, effseq) as (

                                  select emplid, empl_rcd, from_dt, to_dt, per_org, hr_status, effdt, effseq

                                  from   PS_CTS_JOB_DTL1 cross join report_dates

                                  match_recognize(

                                    partition by emplid

                                    order     by effdt, effseq

                                    measures  greatest(start_dt, effdt) as from_dt, nvl(next(effdt) - 1, end_dt) as to_dt

                                    all rows per match

                                    pattern   (a{0,1} b c*)

                                    define    a as effdt <  start_dt, b as effdt >= start_dt, c as effdt <  end_dt + 1

                                  )

                                  where  hr_status = 'A' and per_org = 'CWR'

                              )

                              select emplid, from_dt, to_dt, per_org, hr_status, business_unit, action, effdt

                              from   prep inner join PS_CTS_JOB_DTL using(emplid, empl_rcd, effdt, effseq)

                              where  business_unit in (select business_unit from PS_CTS_IQN_BU)

                                and  from_dt <= to_dt    -- you need to add this line here

                              ;

                               

                              A bit of clarification though: The issue only arises when there are multiple rows in DTL1 where HR_STATUS = 'A' and PER_ORG = 'CWR'; other rows on the same effective date will not participate in the query, and nor should they. And the "most recent" or "highest EFFSEQ" row will be the highest EFFSEQ on that date, only among the rows with HR_STATUS='A' and PER_ORG='CWR', not among ALL the rows for that employee on that date.

                               

                              Note also that the two INSERT statement you provided will indeed cause this issue to be observed, but not for the EMPLID and the EFFDT you show. That's not too important in this case, but in general you must try to make things consistent with each other.

                               

                              In a separate question addresses to Mr. Kulash you talk about additional columns. I hope you are able to add them easily to the MATCH_RECOGNIZE solution, but do feel free to write back if you need help.

                              • 12. Re: Fetching Data Effective date wise by joining three tables
                                mathguy

                                Frank Kulash wrote:

                                 

                                When a row is being displayed from the PS_CTS_JOB_DTL1 table, and it is related to two (or more) rows in the PS_CTS_JOB_DTL table, how do you decide which ACTION (and ACTION_DT, and other columns) to display?

                                 

                                When a row from the PS_CTS_JOB_DTL1 table is related to two (or more) rows in the PS_CTS_IQN_BU table, how do you decide which BUSINESS_UNIT to display?

                                 

                                Rows in the DTL and DTL1 tables are in exact 1-1 correspondence. Both tables have the same primary key composed of FOUR columns, not two: (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) and they have the same values in both tables. I asked the OP about this in Reply 1, and he confirmed in Reply 2.  (And in Reply 6 I pointed out that he must have a typo in one row in the INSERT statements for the DTL table.)

                                 

                                In your solution, you match only on EMPLID and EFFDT, which is wrong. It will lead to questions like the one you asked in Reply 10. I anticipated this difficulty in my comment in the last paragraph of Reply 5. If you write the join correctly, using in particular EFFSEQ, the situation you describe is simply not possible.

                                • 13. Re: Fetching Data Effective date wise by joining three tables
                                  Vemula Muni

                                  Hi mathguy, Actually i am using this query in my PeopleSoft application like below.

                                   

                                  PeopleSoft System will convert the %Bind(CT_IQN_PERD_AET.START_DT) as TO_DATE('2019-06-01','YYYY-MM-DD') and %Bind(CT_IQN_PERD_AET.END_DT) as TO_DATE('2019-06-30','YYYY-MM-DD')

                                   

                                  due to that date format i am getting below error.  Please help me on this.

                                  • 14. Re: Fetching Data Effective date wise by joining three tables
                                    mathguy

                                    If you replace my hard-coded dates with bind variables, you must delete the keyword DATE too, not just the string representing a date. What I had in my code are so-called "date literals" that look like   date '2018-03-12' and the entire expression, including the keyword DATE,   must be replaced with your bind variable.