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

Need to alter Update Query

Vemula Muni
Vemula Muni Member Posts: 57
edited Sep 20, 2019 2:54AM in SQL & PL/SQL

Below is my ps_cts_iqn_empl_wo table structure and data.

                                                                         

EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID
5707161245653011-Aug-1931-Aug-19101545621
5707161245253011-Aug-1931-Aug-19141545621
5707171245151015-Aug-1928-Aug-19151843221 Updated start_dt and end_dt column values in below row
5707171245121010-AUG-1913-AUG-19151843221
5707171245051015-Aug-1928-Aug-19151843221 Updated start_dt and end_dt column values in below row
5707171245051015-JUL-1928-JUL-19151843221
5707181245151015-Aug-1928-Aug-19151843221

                                                                         

and below is my PS_CT_IQN_ACC_STG table structure and data.

                                         

EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id
57071653014-Aug-19
57071751014-Aug-19

I need to update my PS_CT_IQN_ACC_STG table columns (Work_order_Id,End_Dt, Standard_Rate,CTS_WO_OT_RATE, Vendor_id) as below.

Employee id and Project_id should match in both the tables. and report_due_date should be between start_dt and end_dt in ps_cts_iqn_empl_wo.

If report_due_date  not falls between  start_dt and end_dt in ps_cts_iqn_empl_wo table then we nee to check for EMPLID and project_id  combination match.

if there is more than one row matched in ps_cts_iqn_empl_wo  then we need to fetch maximum Work_order_Id row.

first check is to find an maximum active work order  related data(employee id and project_id should match in both tables and report_due_date value between start_dt and end_dt  in ps_cts_iqn_empl_wo ). If there is no row, we should find any previous max work_order_id value for the emplid and project id combination (even report_due_date value not falls between start_dt and end_dt  in ps_cts_iqn_empl_wo).

EMPLIDProject_Idreport_due_dateWork_order_IdEnd_DtStandard_RateCTS_WO_OT_RATE            Vendor_id
57071653014-Aug-191245631-Aug-19101545621
57071751014-Aug-191245113-Aug-19151843221

Create and insert scripts:

CREATE TABLE ps_cts_iqn_empl_wo (EMPLID VARCHAR2(11) NOT NULL,   WORK_ORDER_ID VARCHAR2(15) NOT NULL,   PROJECT_ID VARCHAR2(15) NOT NULL,   START_DT DATE,   END_DT DATE,   STANDARD_RATE DECIMAL(15, 2)  NULL,   CTS_WO_OT_RATE DECIMAL(15, 2)  NULL,   VENDOR_ID VARCHAR2(10)  NULL) ;       Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');     Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621'); Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12451','510',to_date('10-AUG-2019','DD-MON-YYYY'),to_date('13-AUG-2019','DD-MON-YYYY'),15,18,'43221');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12450','510',to_date('15-JUL-2019','DD-MON-YYYY'),to_date('28-JUL-2019','DD-MON-YYYY'),15,18,'43221');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');        CREATE TABLE PS_CT_IQN_ACC_STG (EMPLID VARCHAR2(11) NOT NULL,   PROJECT_ID VARCHAR2(15) NOT NULL,   REPORT_DUE_DATE DATE,   WORK_ORDER_ID VARCHAR2(15)  NULL,   END_DT DATE,   STANDARD_RATE DECIMAL(15, 2)  NULL,   CTS_WO_OT_RATE DECIMAL(15, 2)  NULL,   VENDOR_ID VARCHAR2(10)  NULL) Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);  Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null); 

SQL to modify :

merge INTO PS_CT_IQN_ACC_STG STG USING ps_cts_iqn_empl_wo WO ON (WO.EMPLID = STG.EMPLID

   AND WO.project_id = STG.project_id

   AND stg.report_due_date BETWEEN WO.start_dt AND WO.end_dt) WHEN matched THEN

UPDATE

  SET STG.WORK_ORDER_ID = WO.WORK_ORDER_ID , STG.END_DT = WO.END_DT , STG.STANDARD_RATE = WO.STANDARD_RATE ,STG.CTS_WO_OT_RATE = WO.CTS_WO_OT_RATE, STG.VENDOR_ID = WO.VENDOR_ID

WHERE WO.WORK_ORDER_ID = (

SELECT MAX(WORK_ORDER_ID)

  FROM ps_cts_iqn_empl_wo WO1

WHERE WO1.EMPLID = STG.EMPLID

   AND WO1.project_id = STG.project_id

   AND stg.report_due_date BETWEEN WO1.start_dt AND WO1.END_DT )

Message was edited by: Vemula Muni

Message was edited by: Vemula Muni

Message was edited by: Vemula Muni

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 26, 2019 9:05AM Answer ✓

    Hi,

    Vemula Muni wrote:Below is my ps_cts_iqn_empl_wo table structure and data.EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID5707161245653011-Aug-1931-Aug-191015456215707161245253011-Aug-1931-Aug-191415456215707171245151015-Aug-1928-Aug-191518432215707171245051015-Aug-1928-Aug-191518432215707181245151015-Aug-1928-Aug-19151843221EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID5707161245653011-Aug-1931-Aug-191015456215707161245253011-Aug-1931-Aug-191415456215707171245151015-Aug-1928-Aug-191518432215707181245151015-Aug-1928-Aug-19151843221and below is my PS_CT_IQN_ACC_STG table structure and data.EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id57071653014-Aug-1957071751014-Aug-19I need to update my PS_CT_IQN_ACC_STG table columns (Work_order_Id,End_Dt, Standard_Rate,CTS_WO_OT_RATE, Vendor_id) as below.Employee id and Project_id should match in both the tables. and report_due_date should be between start_dt and end_dt in ps_cts_iqn_empl_wo.If report_due_date not falls between start_dt and end_dt in ps_cts_iqn_empl_wo table then we nee to check for EMPLID and project_id combination match.if there is more than one row matched in ps_cts_iqn_empl_wo then we need to fetch maximum Work_order_Id row.first check is to find an maximum active work order related data(employee id and project_id should match in both tables and report_due_date value between start_dt and end_dt in ps_cts_iqn_empl_wo  ). If there is no row, we should find any previous max work_order_id value for the emplid and project id combination (even report_due_date value not falls between start_dt and end_dt in ps_cts_iqn_empl_wo).EMPLIDProject_Idreport_due_dateWork_order_IdEnd_DtStandard_RateCTS_WO_OT_RATE Vendor_id57071653014-Aug-191245631-Aug-1910154562157071751014-Aug-191245129-Aug-19151843221Create and insert scripts:
    1. CREATETABLEps_cts_iqn_empl_wo(EMPLIDVARCHAR2(11)NOTNULL,
    2. WORK_ORDER_IDVARCHAR2(15)NOTNULL,
    3. PROJECT_IDVARCHAR2(15)NOTNULL,
    4. START_DTDATE,
    5. END_DTDATE,
    6. STANDARD_RATEDECIMAL(15,2)NULL,
    7. CTS_WO_OT_RATEDECIMAL(15,2)NULL,
    8. VENDOR_IDVARCHAR2(10)NULL);
    9. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');
    10. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621');
    11. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');
    12. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','12450','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');
    13. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');
    14. CREATETABLEPS_CT_IQN_ACC_STG(EMPLIDVARCHAR2(11)NOTNULL,
    15. PROJECT_IDVARCHAR2(15)NOTNULL,
    16. REPORT_DUE_DATEDATE,
    17. WORK_ORDER_IDVARCHAR2(15)NULL,
    18. END_DTDATE,
    19. STANDARD_RATEDECIMAL(15,2)NULL,
    20. CTS_WO_OT_RATEDECIMAL(15,2)NULL,
    21. VENDOR_IDVARCHAR2(10)NULL)
    22. InsertintoPS_CT_IQN_ACC_STG(EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570718','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);
    23. InsertintoPS_CT_IQN_ACC_STG(EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);
    CREATE TABLE ps_cts_iqn_empl_wo (EMPLID VARCHAR2(11) NOT NULL,  WORK_ORDER_ID VARCHAR2(15) NOT NULL,  PROJECT_ID VARCHAR2(15) NOT NULL,  START_DT DATE,  END_DT DATE,  STANDARD_RATE DECIMAL(15, 2) NULL,  CTS_WO_OT_RATE DECIMAL(15, 2) NULL,  VENDOR_ID VARCHAR2(10) NULL) ;      Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621');  Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12450','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');       CREATE TABLE PS_CT_IQN_ACC_STG (EMPLID VARCHAR2(11) NOT NULL,  PROJECT_ID VARCHAR2(15) NOT NULL,  REPORT_DUE_DATE DATE,  WORK_ORDER_ID VARCHAR2(15) NULL,  END_DT DATE,  STANDARD_RATE DECIMAL(15, 2) NULL,  CTS_WO_OT_RATE DECIMAL(15, 2) NULL,  VENDOR_ID VARCHAR2(10) NULL)      Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570718','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);  Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null); 
    SQL to modify :merge INTO PS_CT_IQN_ACC_STG STG USING ps_cts_iqn_empl_wo WO ON (WO.EMPLID = STG.EMPLIDAND WO.project_id = STG.project_idAND stg.report_due_date BETWEEN WO.start_dt AND WO.end_dt) WHEN matched THENUPDATESET STG.WORK_ORDER_ID = WO.WORK_ORDER_ID , STG.END_DT = WO.END_DT , STG.STANDARD_RATE = WO.STANDARD_RATE ,STG.CTS_WO_OT_RATE = WO.CTS_WO_OT_RATE, STG.VENDOR_ID = WO.VENDOR_IDWHERE WO.WORK_ORDER_ID = (SELECT MAX(WORK_ORDER_ID)FROM ps_cts_iqn_empl_wo WO1WHERE WO1.EMPLID = STG.EMPLIDAND WO1.project_id = STG.project_idAND stg.report_due_date BETWEEN WO1.start_dt AND WO1.END_DT )Message was edited by: Vemula Muni

    So, you want to update several columns of ps_ct_iqn_acc_stg (or stg, for short) with values taken from a single row of ps_cts_iqn_empl_wo(or wo, for short).  The matching row must have the same emplid and project_id.  When there are multiple matching rows, then you want to choose the best match based n these criteria (in order):

    1. Rows where wo.start_dt <= stg.rport_due_dt <= wo.end_dt are better than other rows
    2. Rows with high work_order_ids are better than rows with lower work_order_ids

    You can use the analytic ROW_NUMBER function assign numbers 1, 2, 3, … to each possible martch, according to the criteria above, and then choose the row numbered 1, like this:

    MERGE INTO  ps_ct_iqn_acc_stg  dstUSING  (           SELECT  wo.emplid, wo.work_order_id, wo.project_id, wo.end_dt           ,       wo.standard_rate, wo.cts_wo_ot_rate, wo.vendor_id           ,       ROW_NUMBER () OVER ( PARTITION BY  wo.emplid                                        ,             wo.project_id                                        ORDER BY      CASE                                                          WHEN  stg.report_due_date BETWEEN wo.start_dt                                                                                    AND     wo.end_dt                                                          THEN  1                                                          ELSE  2                                                      END                                        ,             wo.work_order_id  DESC                                      )  AS r_num           FROM    ps_ct_iqn_acc_stg   stg           JOIN    ps_cts_iqn_empl_wo  wo  ON   wo.emplid      = stg.emplid                                           AND  wo.project_id  = stg.project_id       )                       srcON  (    src.emplid      = dst.emplid    AND  src.project_id  = dst.project_id    AND  src.r_num       = 1    )WHEN MATCHED  THEN UPDATESET  dst.work_order_id  = src.work_order_id,    dst.end_dt         = src.end_dt,    dst.standard_rate  = src.standard_rate,    dst.cts_wo_ot_rate = src.cts_wo_ot_rate,    dst.vendor_id      = src.vendor_id;

    The results I get are different from shat you posted.  In the sample data, the emplids in stg are '570717' and '570718', but in your desired results the emplids are '570716' and '570717'.  I suspect there is a mistake in the posted data or results.

    EDIT: The reply above was written before I could see any replies.  I see that you already clarified the typo, and that Ascheffer has posted a similar solution (reply #4), using the aggregate LAST function instead of the analytic ROW_NUMBER function.

    Vemula Muni
«1

Answers

  • Jeevan Anand
    Jeevan Anand Member Posts: 52 Red Ribbon
    edited Aug 26, 2019 8:04AM

    Employee id and Project_id should match in both the tables. and report_due_date should be between start_dt and end_dt in ps_cts_iqn_empl_wo.

    If report_due_date  not falls between  start_dt and end_dt in ps_cts_iqn_empl_wo table then we nee to check for EMPLID and project_id  combination match.

    Few open points before proceed further:

    If report date not falls between start data and end date, EMP id and Project id should be considered. Suppose report falls between start and end date and having two entries means, how to proceed further? 

    If your answer is "max product id", then i feel, there is no logic in validating report date, because , both scenario will come under Max product id

  • Jeevan Anand
    Jeevan Anand Member Posts: 52 Red Ribbon
    edited Aug 26, 2019 8:12AM

    EMPL_ID: 570718 is mapped to Project_id: 530 in table:ps_cts_iqn_empl_wo, but in source table, it is mapped to 510. Can you provide the valid input details?

  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 26, 2019 8:32AM

    Hi @AJ. Below is inputs to your quires.

    Few open points before proceed further:

    EMP id and Project id  matched and report date falls between start data and end date,  and having more than one entries , how to proceed further?  Need to update MAX(WORK_ORDER_ID ) row data from ps_cts_iqn_empl_wo

    If report date not falls between start data and end date, EMP id and Project id matched. if having more than one entry then  ,Need to update MAX(WORK_ORDER_ID ) row data from ps_cts_iqn_empl_wo

    EMPL_ID: 570718 is mapped to Project_id: 530 in table:ps_cts_iqn_empl_wo, but in source table, it is mapped to 510. Can you provide the valid input details? typo error employee id is 570716.

  • Anton Scheffer
    Anton Scheffer Senior Java Developer NieuwegeinMember Posts: 1,950 Gold Trophy
    edited Aug 26, 2019 8:33AM

    update PS_CT_IQN_ACC_STG stg

    set ( stg.work_order_id, stg.end_dt, stg.standard_rate, stg.cts_wo_ot_rate, stg.vendor_id ) =

      ( select max( wo.work_order_id ) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id ) work_order_id

           , max( wo.end_dt ) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id ) end_dt

           , max( wo.standard_rate) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id ) standard_rate

           , max( wo.cts_wo_ot_rate) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id ) cts_wo_ot_rate

           , max( wo.vendor_id ) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id )

      from ps_cts_iqn_empl_wo wo

      where wo.emplid = stg.emplid

      and   wo.project_id = stg.project_id

    )

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 26, 2019 9:05AM Answer ✓

    Hi,

    Vemula Muni wrote:Below is my ps_cts_iqn_empl_wo table structure and data.EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID5707161245653011-Aug-1931-Aug-191015456215707161245253011-Aug-1931-Aug-191415456215707171245151015-Aug-1928-Aug-191518432215707171245051015-Aug-1928-Aug-191518432215707181245151015-Aug-1928-Aug-19151843221EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID5707161245653011-Aug-1931-Aug-191015456215707161245253011-Aug-1931-Aug-191415456215707171245151015-Aug-1928-Aug-191518432215707181245151015-Aug-1928-Aug-19151843221and below is my PS_CT_IQN_ACC_STG table structure and data.EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id57071653014-Aug-1957071751014-Aug-19I need to update my PS_CT_IQN_ACC_STG table columns (Work_order_Id,End_Dt, Standard_Rate,CTS_WO_OT_RATE, Vendor_id) as below.Employee id and Project_id should match in both the tables. and report_due_date should be between start_dt and end_dt in ps_cts_iqn_empl_wo.If report_due_date not falls between start_dt and end_dt in ps_cts_iqn_empl_wo table then we nee to check for EMPLID and project_id combination match.if there is more than one row matched in ps_cts_iqn_empl_wo then we need to fetch maximum Work_order_Id row.first check is to find an maximum active work order related data(employee id and project_id should match in both tables and report_due_date value between start_dt and end_dt in ps_cts_iqn_empl_wo  ). If there is no row, we should find any previous max work_order_id value for the emplid and project id combination (even report_due_date value not falls between start_dt and end_dt in ps_cts_iqn_empl_wo).EMPLIDProject_Idreport_due_dateWork_order_IdEnd_DtStandard_RateCTS_WO_OT_RATE Vendor_id57071653014-Aug-191245631-Aug-1910154562157071751014-Aug-191245129-Aug-19151843221Create and insert scripts:
    1. CREATETABLEps_cts_iqn_empl_wo(EMPLIDVARCHAR2(11)NOTNULL,
    2. WORK_ORDER_IDVARCHAR2(15)NOTNULL,
    3. PROJECT_IDVARCHAR2(15)NOTNULL,
    4. START_DTDATE,
    5. END_DTDATE,
    6. STANDARD_RATEDECIMAL(15,2)NULL,
    7. CTS_WO_OT_RATEDECIMAL(15,2)NULL,
    8. VENDOR_IDVARCHAR2(10)NULL);
    9. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');
    10. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621');
    11. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');
    12. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','12450','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');
    13. Insertintops_cts_iqn_empl_wo(EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');
    14. CREATETABLEPS_CT_IQN_ACC_STG(EMPLIDVARCHAR2(11)NOTNULL,
    15. PROJECT_IDVARCHAR2(15)NOTNULL,
    16. REPORT_DUE_DATEDATE,
    17. WORK_ORDER_IDVARCHAR2(15)NULL,
    18. END_DTDATE,
    19. STANDARD_RATEDECIMAL(15,2)NULL,
    20. CTS_WO_OT_RATEDECIMAL(15,2)NULL,
    21. VENDOR_IDVARCHAR2(10)NULL)
    22. InsertintoPS_CT_IQN_ACC_STG(EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570718','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);
    23. InsertintoPS_CT_IQN_ACC_STG(EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID)values('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);
    CREATE TABLE ps_cts_iqn_empl_wo (EMPLID VARCHAR2(11) NOT NULL,  WORK_ORDER_ID VARCHAR2(15) NOT NULL,  PROJECT_ID VARCHAR2(15) NOT NULL,  START_DT DATE,  END_DT DATE,  STANDARD_RATE DECIMAL(15, 2) NULL,  CTS_WO_OT_RATE DECIMAL(15, 2) NULL,  VENDOR_ID VARCHAR2(10) NULL) ;      Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621');  Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12450','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');   Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');       CREATE TABLE PS_CT_IQN_ACC_STG (EMPLID VARCHAR2(11) NOT NULL,  PROJECT_ID VARCHAR2(15) NOT NULL,  REPORT_DUE_DATE DATE,  WORK_ORDER_ID VARCHAR2(15) NULL,  END_DT DATE,  STANDARD_RATE DECIMAL(15, 2) NULL,  CTS_WO_OT_RATE DECIMAL(15, 2) NULL,  VENDOR_ID VARCHAR2(10) NULL)      Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570718','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);  Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null); 
    SQL to modify :merge INTO PS_CT_IQN_ACC_STG STG USING ps_cts_iqn_empl_wo WO ON (WO.EMPLID = STG.EMPLIDAND WO.project_id = STG.project_idAND stg.report_due_date BETWEEN WO.start_dt AND WO.end_dt) WHEN matched THENUPDATESET STG.WORK_ORDER_ID = WO.WORK_ORDER_ID , STG.END_DT = WO.END_DT , STG.STANDARD_RATE = WO.STANDARD_RATE ,STG.CTS_WO_OT_RATE = WO.CTS_WO_OT_RATE, STG.VENDOR_ID = WO.VENDOR_IDWHERE WO.WORK_ORDER_ID = (SELECT MAX(WORK_ORDER_ID)FROM ps_cts_iqn_empl_wo WO1WHERE WO1.EMPLID = STG.EMPLIDAND WO1.project_id = STG.project_idAND stg.report_due_date BETWEEN WO1.start_dt AND WO1.END_DT )Message was edited by: Vemula Muni

    So, you want to update several columns of ps_ct_iqn_acc_stg (or stg, for short) with values taken from a single row of ps_cts_iqn_empl_wo(or wo, for short).  The matching row must have the same emplid and project_id.  When there are multiple matching rows, then you want to choose the best match based n these criteria (in order):

    1. Rows where wo.start_dt <= stg.rport_due_dt <= wo.end_dt are better than other rows
    2. Rows with high work_order_ids are better than rows with lower work_order_ids

    You can use the analytic ROW_NUMBER function assign numbers 1, 2, 3, … to each possible martch, according to the criteria above, and then choose the row numbered 1, like this:

    MERGE INTO  ps_ct_iqn_acc_stg  dstUSING  (           SELECT  wo.emplid, wo.work_order_id, wo.project_id, wo.end_dt           ,       wo.standard_rate, wo.cts_wo_ot_rate, wo.vendor_id           ,       ROW_NUMBER () OVER ( PARTITION BY  wo.emplid                                        ,             wo.project_id                                        ORDER BY      CASE                                                          WHEN  stg.report_due_date BETWEEN wo.start_dt                                                                                    AND     wo.end_dt                                                          THEN  1                                                          ELSE  2                                                      END                                        ,             wo.work_order_id  DESC                                      )  AS r_num           FROM    ps_ct_iqn_acc_stg   stg           JOIN    ps_cts_iqn_empl_wo  wo  ON   wo.emplid      = stg.emplid                                           AND  wo.project_id  = stg.project_id       )                       srcON  (    src.emplid      = dst.emplid    AND  src.project_id  = dst.project_id    AND  src.r_num       = 1    )WHEN MATCHED  THEN UPDATESET  dst.work_order_id  = src.work_order_id,    dst.end_dt         = src.end_dt,    dst.standard_rate  = src.standard_rate,    dst.cts_wo_ot_rate = src.cts_wo_ot_rate,    dst.vendor_id      = src.vendor_id;

    The results I get are different from shat you posted.  In the sample data, the emplids in stg are '570717' and '570718', but in your desired results the emplids are '570716' and '570717'.  I suspect there is a mistake in the posted data or results.

    EDIT: The reply above was written before I could see any replies.  I see that you already clarified the typo, and that Ascheffer has posted a similar solution (reply #4), using the aggregate LAST function instead of the analytic ROW_NUMBER function.

    Vemula Muni
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 26, 2019 9:20AM

    Hi,

    A couple of things to consider:

    • Do you really want to replicate so many columns?  If you ever update either table, then you'll need to check if the replicated values are still accurate, and, if not, fix them.  Maybe it would be better to replicate just the work_order_id, or not to replicate anything, and just have a view with the values that are in both tables.
    • Name columns consistently.  If you abbreviate DATE as DT in some columns, then do it in all columns. REPORT_DUE_DATE is a good column name, but you have another column called START_DT (and vice versa).
    • Only update rows that really are changing.  For example, in reply #5 I assumed that work_order_id was NULL in the destination table.  If that's not the case, you should add a WHERE clause to not do the update if all of the columns are identical.  This goes whether you use MERGE or UPDATE.
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    edited Aug 26, 2019 9:46AM

    I have one question about the business meaning of a specific instruction in your problem statement. If you are not 100% sure of the answer, you would do well to ask the business users who asked you to solve this problem in code for them.

    You said this:  If there are rows where the "report date" falls between the work order "start date" and "end date", use only those rows and ignore any other rows (for the same employee/project combination). But if no rows (for the same employee/project) satisfy this condition, then consider all the rows for the employee/project. This means all the rows where the END DATE was before the report date AND all the rows where the START DATE is after the report date.

    This makes no sense to me. If you had said "if no match with report date between work order start and end, then consider all the rows with start date after report date" that would make sense. Or, if you had said the opposite ("if no match then consider all rows where end date was before the report date") that would also make sense. But considering all rows where the work order was completed before the report date AND all the rows where the work will begin in the future, but do that only when there are no rows where the work order is "current" on the report date, seems to me illogical. It is more likely that you didn't understand the exact requirement, or that it wasn't stated correctly by the business user, or that the business user himself made a mistake - they didn't think it through and gave you a wrong requirement. Best to check as soon as possible, before you work too long on the wrong problem.

  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 26, 2019 10:13AM

    Hi @Frank Kulash,

    Could you please let me know how above query will work for below situation.

    If report date not falls between start data and end date,  and EMP id and Project id matched. if having more than one entry then  ,Need to update MAX(WORK_ORDER_ID ) row data from ps_cts_iqn_empl_wo.

  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 26, 2019 10:38AM

    Hi @mathguy,

    Case 1:

    ----------

    When employee id and project id matched and  report_due_date falls between start_dt and end_dt. and more than one row present for this combination then i need to update max(work_order_id) row values in target.

    EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID
    5707161245653011-Aug-1931-Aug-19101545621
    5707161245253011-Aug-1931-Aug-19141545621

    EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id
    57071653014-Aug-19

    in above example for employee 570716 ,two rows will match for 14-AUG-19 report_due_date value. So i want to fetch max(work_order_id) i.e 12456 row to update in target.

    Case 2 :

    -------

    When employee id and project id matched and  report_due_date not falls between start_dt and end_dt. i need to fetch a maximum work_order_id row and Work_order_Id end_dt should be less than report_due_date.

    EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID
    5707171245151010-Aug-1913-Aug-19151843221
    5707171245051015-JUL-1928-JUL-19151843221

    EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id
    57071751014-Aug-19

    in above example also two rows will match , so i need to fetch 12451 work_order_id row to update in target.

  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    edited Aug 26, 2019 10:49AM

    This makes much more sense.

    What you did, though, is wrong. If you need to alter your original post, you should do something like this (by way of a simple example):

    Original text (which is incorrect):

    Five times five is twenty-two

    Corrected text:

    Five times five is twenty-two  twenty-five  (edited to correct the statement)

    (Yes, INCLUDE the clarification about an "edit", so that whoever reads the post can see that you edited it, and why.)

    Instead, what you did was to simply overwrite what you had in the original post. That is an absolute no-no. You should never do that. Actually some board members will be more radical: they will tell you never to edit your post, and to make any changes in further replies. I am not a big fan of that, but if you edit your original post, that should be crystal clear to anyone who reads the thread in the future.