In this step, a view created as on the top of the base table employee and the external table employee_extnl, so that only the latest data is shown. This view would need to be then loaded into a final reporting table which will be accessed by the subscribers of the data.
Use the following SQL:
CREATE VIEW employee_view AS SELECT t1.* FROM (SELECT * FROM employee UNION ALL SELECT * FROM employee_extnl) t1 JOIN (SELECT employeeid, max(record_datetime) max_modified FROM (SELECT * FROM employee UNION ALL SELECT * FROM employee_extnl) t2 GROUP BY employeeid) s ON t1.employeeid = s.employeeid AND t1.record_datetime = s.max_modified;
The results from the view are as follow:
The view generates only the latest data.