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.
with table_history as --this table has multiple records per UW_NAME with latest record showing EXPIRATION_DATE of '12/31/9999'
(
select 'LARRY' as UW_NAME, '1/2/2015' as EFFECTIVE_DATE , '1/30/2019' as EXPIRATION_DATE , 'SEAN' as UW_MANAGER_NAME from dual Union all
select 'LARRY' as UW_NAME, '2/1/2019' as EFFECTIVE_DATE , '11/15/2019' as EXPIRATION_DATE , 'SEAN' as UW_MANAGER_NAME from dual Union all
select 'LARRY' as UW_NAME, '11/16/2019' as EFFECTIVE_DATE , '12/31/9999' as EXPIRATION_DATE , 'SEAN' as UW_MANAGER_NAME from dual Union all
select 'JOE' as UW_NAME, '8/1/2017' as EFFECTIVE_DATE , '12/31/9999' as EXPIRATION_DATE , 'CRYSTAL' as UW_MANAGER_NAME from dual Union all
select 'JACK' as UW_NAME, '6/1/2018' as EFFECTIVE_DATE , '12/31/9999' as EXPIRATION_DATE , 'BILL' as UW_MANAGER_NAME from dual
)
select * from table_history
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.
As you can notice the 'Latest' manager for 'LARRY' and 'JACK' has changed when compared to the above history table.
with table_current as --this table always has 1 record per UW_NAME
(
select 'LARRY' as UW_NAME, 'MARK' as UW_MANAGER_NAME, '12/1/2020' AS UPDATE_DATE from dual Union all
select 'JOE' as UW_NAME, 'CRYSTAL' as UW_MANAGER_NAME, '12/1/2020' AS UPDATE_DATE from dual Union all
select 'JACK' as UW_NAME, 'SARAH' as UW_MANAGER_NAME, '12/1/2020' AS UPDATE_DATE from dual
)
select * from table_current
Requirement: 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'
Expected Final table:
with table_new as
(
select 'LARRY' as UW_NAME, '1/2/2015' as EFFECTIVE_DATE , '1/30/2019' as EXPIRATION_DATE , 'SEAN' as UW_MANAGER_NAME from dual Union all
select 'LARRY' as UW_NAME, '2/1/2019' as EFFECTIVE_DATE , '11/15/2019' as EXPIRATION_DATE , 'SEAN' as UW_MANAGER_NAME from dual Union all
select 'LARRY' as UW_NAME, '11/16/2019' as EFFECTIVE_DATE , '12/1/2020' as EXPIRATION_DATE , 'SEAN' as UW_MANAGER_NAME from dual Union all
select 'LARRY' as UW_NAME, '12/1/2020' as EFFECTIVE_DATE , '12/31/9999' as EXPIRATION_DATE , 'MARK' as UW_MANAGER_NAME from dual Union all
select 'JOE' as UW_NAME, '8/1/2017' as EFFECTIVE_DATE , '12/31/9999' as EXPIRATION_DATE , 'CRYSTAL' as UW_MANAGER_NAME from dual Union all
select 'JACK' as UW_NAME, '6/1/2018' as EFFECTIVE_DATE , '12/1/2020' as EXPIRATION_DATE , 'BILL' as UW_MANAGER_NAME from dual Union all
select 'JACK' as UW_NAME, '12/1/2020' as EFFECTIVE_DATE , '12/31/9999' as EXPIRATION_DATE , 'SARAH' as UW_MANAGER_NAME from dual
)
select * from table_new
Sounds like something an ETL tool would typically handle but wondering if i can use Oracle to achieve this? Please let me know your thoughts.
My oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production