Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need to alter Update Query

Vemula MuniAug 26 2019 — edited Sep 20 2019

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

This post has been answered by Frank Kulash on Aug 26 2019
Jump to Answer
Comments
Post Details
Added on Aug 26 2019
13 comments
405 views