Snapshot of table row instance with time intervals

Database
Teradata Employee

Snapshot of table row instance with time intervals

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:

 

 

History Problem.PNG

 

 

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?

Tags (1)
1 REPLY
Teradata Employee

Re: Snapshot of table row instance with time intervals

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.