I have a large employee table with lots of columns and versions and one org lookup table also with some versions.
I need to update a column org_id in employee table which is all nulls now with no history.
An employee can have multiple versions records each with same empid but same or different rc_code.
Emp_id, emp_ver_id(pk) ,as_of_date, end_date,rc_code, org_id
Emp_ver_id is identity.
Org_ver_id(pk), Org_id , Rc_code, as_of_date, end_date
org_ver_id is identity
I need to update all org_id under each employee records with the earliest rc_code/ org_id for that employee based on as_of_date which is earliest for that employee record.
problem : due to row by row operation lookup between 2 tables on rc code gives different org_id value which is not the earliest one.
sample result needed is below -
1. 1 22. 01/01/2020. 01
2. 1. 22. 02/01/2020. 03
3. 1. 22 03/01/2020. 04
4. 2. 32 01/01/2019. 13
5. 2. 32 04/09/2019. 14