SQL & PL/SQL

Below is my ps_cts_iqn_empl_wo table structure and data.

and below is my PS_CT_IQN_ACC_STG table structure and data.

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

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