Source table Data : ps_cts_iqn_empl_wo
| EMPLID | Work_order_Id | Project_Id | Start_Dt | End_Dt | Standard_Rate | CTS_WO_OT_RATE | Vendor_ID |
| 570716 | 12456 | 530 | 11-Aug-19 | 31-Aug-19 | 10 | 15 | 45621 |
| 570716 | 12452 | 530 | 11-Aug-19 | 31-Aug-19 | 14 | 15 | 45621 |
| 570717 | 12451 | 210 | 10-AUG-19 | 13-AUG-19 | 15 | 18 | 43221 |
| 570717 | 12450 | 510 | 15-JUL-19 | 28-JUL-19 | 15 | 18 | 43221 |
| 570718 | 12451 | 510 | 15-Aug-19 | 28-Aug-19 | 15 | 18 | 43221 |
Target Table :
| EMPLID | Project_Id | report_due_date | Work_order_Id | WO_End_Dt | WO_Standard_Rate | WO_OT_Rate | Vendor_id |
| 570716 | 530 | 14-Aug-19 | | | | | |
| 570716 | 530 | 13-Aug-19 | | | | | |
| 570717 | 510 | 14-Aug-19 | | | | | |
Required Output :
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.
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.
| EMPLID | Project_Id | report_due_date | Work_order_Id | End_Dt | Standard_Rate | CTS_WO_OT_RATE | Vendor_id |
| 570716 | 530 | 14-Aug-19 | 12456 | 31-Aug-19 | 10 | 15 | 45621 |
| 570716 | 530 | 13-Aug-19 | 12456 | 31-Aug-19 | 10 | 15 | 45621 |
| 570717 | 510 | 14-Aug-19 | 12451 | 13-Aug-19 | 15 | 18 | 43221 |
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 ('570716','530',to_date('13-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);
Query
UPDATE PS_CT_IQN_ACC_STG stg
SET (Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_Rate,Vendor_id) = (select Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_RATE,Vendor_ID from ps_cts_iqn_empl_wo WO1
where WO1.Work_order_Id= (select max(Work_order_Id) from ps_cts_iqn_empl_wo WO where WO.EMPLID = STG.EMPLID
AND WO.project_id = STG.project_id and stg.report_due_date between WO.start_dt and WO.end_dt ))
Message was edited by: Vemula Muni