Unfortunately, the requirement does not seem to be clear.
Can you please elaborate a bit ?
scenario1 ID PLAN_ID doc_type Delivery_type change_dt plan_id ID expected 123 62345 xyz home 12/13/2020 12345 123 planid delivery 123 * xyz office null 62345 123 12345 home 87654 123 62345 office 87654 office scenario2 ID PLAN_ID doc_type Delivery_type change_dt plan_id ID expected 123 62345 xyz home 12/13/2020 12345 123 planid delivery 123 * xyz office 12/10/2020 62345 123 12345 office 123 12345 xyz home 8/12/2020 87654 123 62345 home 87654 office
"if suppose * has the highest change_dt in Table A then all the plans in Table B should have the delivery type same as * in Table A"
This can be achieved by fetching the max(change_dt) from the source and then joining it with the data in Table B based on the join condition (like plan_id).
"if suppose any planId in Table A is having highest change date then only the corresponding plan delivery type should be assigned rest all other plans should have same as * delivery type like in scenario 2"
Can you please explain the requirement here ?
Please do elaborate on the requirement when you get a chance.
If you might have implemented/resolved this already, please do post the solution here so that it might be helpful for others later on.