INSERT new record/End date older record when value changes

[konquistador12]

Hi all, could use some help. Not able to figure out a way to achieve the below requirement. 

This is my scenario

I have a employee history table that is static(ie doesnt change/no data loads happen to this table). This table tracks the Underwriter(employee) history when the Manager changes with the help of Effective and Expiration dates. This table has multiple records per UW_NAME with latest record showing EXPIRATION_DATE of '12/31/9999'

emp_history table:

I also have another table that is loaded with new data on a daily basis. This table has the most current assignments of the Underwriters and their Managers and shows only the LATEST Manager names. This table always has 1 record per UW_NAME

As you can notice the 'Latest' manager for 'LARRY' and 'JACK' has changed when compared to the above history table.

Expected Result in a New Table:

Have to create a brand new table that maintains the format of table_history but pick up the latest 'Manager' from table_current. Have to update the Expiration_Date with the 'UPDATE_DATE' and create a new record with the latest manager name with the expiration_Date of '12/31/9999'

I have loaded the History table(table1) and Current table(table2) into oracle. Any thoughts what components to use next?

I am using Talend Data Integration V 7.3.1

Thanks a lot!

[David_Beaty]

Hi,

You need to use the t{database type}SCD component.

Thanks

David