I am not sure what to call this phenomenon. I have just titled it with "Snapshot of table row instance with time intervals". We have a situation where we need to create a new entity which is kind of JOIN of two tables but these two tables have history and that history we need to merge into this entity. Please have a look at example below:
In the image you can see we have two tables ABC and PQR with ID as key column. We need to create one table ABCPQR which will be join of this both table and they history of this table will contains merged history of both attribute from their respective tables.
How to implement this in database? I am using Teradata as database, Is there any algorithm available to achieve this?
Use the OVERLAPS operator to compare the date ranges, and CASE expressions to choose the later start date & earlier end date in the result row.