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

Fetching Contract Employee information by Join the JOB_DTL,JOB_DTL1,IQN_BU Tables.

Vemula Muni
Vemula Muni Member Posts: 57
edited Aug 6, 2019 1:27PM in SQL & PL/SQL

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)

JOB Table
EMPLIDBUSINESS_UNITEMPL_RCDEFFDTEFFSEQACTION
733930US410027-May-190ADD
733930US410002-Jun-190DTA
733930US41004-Jun-190TER
733930US41016-Jun-190HIR
733930US410115-Jun-190TER
733930US410215-Jun-191ADD
733930US608219-Jun-190ASC
733930CN512226-Jun-190ASG
733931US410026-Apr-190HIR
733931US41005-Jun-190TER
733931IN20816-Jun-190ADD
733931IN208110-Jun-190TER
733931IN208212-Jun-190HIR
733931IN408218-Jun-190ASC
733931IN408219-Jun-190TER
733931IN208326-Jun-190ADD
733931IN208328-Jun-190DTA

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 )

EMPL_IDEMPL_RCDEFFDTEFFSEQHR_STATUSPER_ORG
733930027-May-190ACWR
733930002-Jun-190ACWR
73393004-Jun-190ICWR
73393016-Jun-190AEMP
733930115-Jun-190IEMP
733930215-Jun-191ACWR
733930219-Jun-190ACWR
733930226-Jun-190ACWR
733931026-Apr-190AEMP
73393105-Jun-190IEMP
73393116-Jun-190ACWR
733931110-Jun-190ICWR
733931212-Jun-190AEMP
733931218-Jun-190AEMP
733931219-Jun-190IEMP
733931326-Jun-190ACWR
733931328-Jun-190ADTA

Below is my "PS_CTS_IQN_BU"  table structure and data.

IQN BU Setp
CountryBusiness_Unit
USAUS410
USAUS412
CANCN512
INDIN208

Based on above three tables i need to fetch below output. When i pass from date and to date in query (month start and end dates i.e like '01-JUN-19' and '30-JUN-19' ) , query should fetch employee active from active to dates as CWR and that employee Business unit should be in PS_CTS_IQN_BU table.

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 as CWR  in between '19-JUN-19' to '25-JUN-19' in different BU which not in IQN_BU setup table, so i want to exclude this information.

EMPLIDFROM_DATETo_DATEPER_ORGHR_STATUSReason
7339301-Jun-193-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393015-Jun-1918-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393026-Jun-1930-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
7339316-Jun-199-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393126-Jun-1930-JUN-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table

PS_CTS_JOB_DTL Table Build and Insert Scripts: 

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

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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),0,'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');
Tagged:
KayK[Deleted User]
· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 1, 2019 11:23PM Answer ✓

    Hi,

    Since you're using Oracle 12, you can take advantage of the OUTER APPLY feature.  Here's one way:

    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 reasonFROM      got_business_unit  gOUTER 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'            )                eWHERE     hr_status  = 'A'AND       e.enddt    > g.startdtORDER BY  emplid,         startdt;

    The output from your sample data is just what you requested:

    EMPLID      STARTDT     ENDDT       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 table733930      15-Jun-2019 18-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table733930      26-Jun-2019 30-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table733931      06-Jun-2019 09-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table733931      26-Jun-2019 30-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table

    You want a row of output for each row in the table that has hr_status='A' (and is in the given date range, and has the given per_org).  However, if a row does not have any matching row in ps_cts_iqn_bu, then you want to count its hr_status as 'I'.
    The sub-query called b above finds one of the matching business_units in ps_cts_iqn_bu, if there is one.  It's not important which one it finds, you just need to know if it found any.
    The sub-query called e finds the 'I' row corresponding to each 'A' row, that is, the earliest 'I' row matching each 'A' row.  If there is no matching 'I' row, that means the emplid is still active, so act as if the inactive date is far in the future.

    · Share on TwitterShare on Facebook
«1

Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 1, 2019 7:09AM

    Hi,

    Would you like to get answers that work?  Then make sure the CREATE TABLE and INSERT statements you post work, too.  Test (and, if necessary, fix) them before you post them. A lot of the INSERT statements you posted fail.

    Also make sure the results you post are exactly what you want.  Do you want a row that has hr_status='This Row not required ...'?  If that was just a comment, don't include it in the result set; put comments before or after the results.  Do you really want "31-JUN" as a date?

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Aug 1, 2019 7:47AM 

    PS_CTS_JOB_DTL Table Build and Insert Scripts:
    1. CREATETABLEPS_CTS_JOB_DTL(EMPLIDVARCHAR2(11)NOTNULL,
    2. BUSINESS_UNITVARCHAR2(5)NOTNULL,
    3. EMPL_RCDSMALLINTNOTNULL,
    4. EFFDTDATE,
    5. EFFSEQSMALLINTNOTNULL,
    6. ACTIONVARCHAR2(3)NOTNULL);
    7. InsertintoPS_CTS_JOB_DTL(EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION,ACTION_DT)values('733930','US410',0,to_date('27-MAY-19','DD-MON-YY'),0,'ADD');

    'Those who cannot remember the past are condemned to repeat it.' (George Santayana-1905)

    Does the term "Y2K" mean anything to you?  Do you remember 1999 and 2000?

    20 years ago I and armies of guys like me were busting our butts to insure that most of the computer programs in the world did not break shortly after 12:01 AM  on January 1, 2000.

    TWENTY ******* YEARS AGO!

    It's long, long, long past time to quit using 2-digit years and specify 4-digit years.  Every time.  EVERY TIME!

    KayK[Deleted User]
    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 1, 2019 7:59AM

    Hi @Frank Kulash, Create table and insert statements are updated.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 1, 2019 9:23PM

    Hi,

    Vemula Muni wrote:Hi Frank Kulash, Create table and insert statements are updated.

    Okay; what about the desired results?  If you really want '31-JUN-19' in the output, then explain.

    (I bet that's a mistake, but if you posted that mistake even after it was pointed out in reply #1, then there's a good chance the posted results have other mistakes.)

    Employee is active as CWR and BU is in IQN_BU setup table
    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 1, 2019 11:14PM

    Hi @Frank Kulash , Sorry for the incorrect date. It should be 30-Jun-19. Edited the same.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 1, 2019 11:23PM Answer ✓

    Hi,

    Since you're using Oracle 12, you can take advantage of the OUTER APPLY feature.  Here's one way:

    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 reasonFROM      got_business_unit  gOUTER 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'            )                eWHERE     hr_status  = 'A'AND       e.enddt    > g.startdtORDER BY  emplid,         startdt;

    The output from your sample data is just what you requested:

    EMPLID      STARTDT     ENDDT       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 table733930      15-Jun-2019 18-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table733930      26-Jun-2019 30-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table733931      06-Jun-2019 09-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table733931      26-Jun-2019 30-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table

    You want a row of output for each row in the table that has hr_status='A' (and is in the given date range, and has the given per_org).  However, if a row does not have any matching row in ps_cts_iqn_bu, then you want to count its hr_status as 'I'.
    The sub-query called b above finds one of the matching business_units in ps_cts_iqn_bu, if there is one.  It's not important which one it finds, you just need to know if it found any.
    The sub-query called e finds the 'I' row corresponding to each 'A' row, that is, the earliest 'I' row matching each 'A' row.  If there is no matching 'I' row, that means the emplid is still active, so act as if the inactive date is far in the future.

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

    Hi @Frank Kulash, According to above data employee 733930 is active from 01-JUN-2019 to 03-JUN-19. after that i am inserted one more row with effdt '02-JUN-19' with HR status 'A'. ( in this case also query should fetch only one for 01-JUN-2019 to 03-JUN-19 ) but query is fetching from '01-Jun-19' to '03-Jun-19' one row, and '02-Jun-19' to '03-Jun-19'.

    For employee 733931 i am inserted one more with effdt '28-JUN-19' and HR status is Active. in this case also query should fetch one row from 26-JUN-19 to 30-Jun-19,  But query is fetching from 26-Jun-19 to 28-Jun-19.

    New Insert Statements:  ( newly added rows for existing data, same statements included in question as well )

    Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-19','DD-MON-YY'),0,'DTA');

    Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-19','DD-MON-YY'),0,'DTA');

    Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('02-JUN-19','DD-MON-YY'),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-19','DD-MON-YY'),0,'A','CWR'); 

    Out put from the System :

    pastedImage_3.png

    Expected Output :

    EMPLIDFROM_DATETo_DATEPER_ORGHR_STATUSReason
    7339301-Jun-193-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    73393015-Jun-1918-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    73393026-Jun-1930-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    7339316-Jun-199-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    73393126-Jun-1930-JUN-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 2, 2019 8:08AM

    Hi,

    Vemula Muni wrote:Hi Frank Kulash, According to above data employee 733930 is active from 01-JUN-2019 to 03-JUN-19. after that i am inserted one more row with effdt '02-JUN-19' with HR status 'A'. ( in this case also query should fetch only one for 01-JUN-2019 to 03-JUN-19 ) but query is fetching from '01-Jun-19' to '03-Jun-19' one row, and '02-Jun-19' to '03-Jun-19'.For employee 733931 i am inserted one more with effdt '28-JUN-19' and HR status is Active. in this case also query should fetch one row from 26-JUN-19 to 30-Jun-19, But query is fetching from 26-Jun-19 to 28-Jun-19.New Insert Statements: ( newly added rows for existing data, same statements included in question as well )Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-19','DD-MON-YY'),0,'DTA'); Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-19','DD-MON-YY'),0,'DTA'); Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('02-JUN-19','DD-MON-YY'),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-19','DD-MON-YY'),0,'A','CWR'); Out put from the System :
    pastedImage_3.png

    Expected Output :

    EMPLIDFROM_DATETo_DATEPER_ORGHR_STATUSReason
    7339301-Jun-193-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    73393015-Jun-1918-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    73393026-Jun-1930-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    7339316-Jun-199-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
    73393126-Jun-1930-JUN-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table

    How was anyone supposed to know this from reading your original message?  Don't wat until reply #7 to give your requirements: say exactly what you want from the beginning.

    I made a guess earlier.  I guessed wrong, and wasted  lot of time.  Don't make people guess.  Describe exactly what you want (including what each row in the result set represents) and how you get the desired results from your sample data.  Include examples of any special cases (such multiple consecutive rows with hr_status='A') in your sample data, results and explanation.

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

    Hi @Frank Kulash, Sorry for giving trouble. there is a chance to have an employee multiple consecutive rows with hr_status='A' when his Data ,location ,assignment and pay scale changed.

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

    Hi @Frank Kulash , Please take me out from this issue. Due to this issue my development got struck.

    · Share on TwitterShare on Facebook