Merge History tables and alligning the time periods

Database

Merge History tables and alligning the time periods

Hi Team,

I need your expert advice to find an the optimal solution for the below problem.

I have 1 driver table and 6 tables with 15-20 years history. Good thing, the key column is same in all..

The requirement is to extract few attributes for the key column from each of these history tables and populate into one table for reporting purposes..

Obviously the date ranges are varying for each row. So while loading into one table I would like to list all the required columns WITH VALUE that have a overlapping period, else Null.

Example below

TABLE1

id            Col1  STRT_D       END_D

ABCD123  01  01/01/2001 11/01/2001

ABCD123  02  12/01/2001 31/12/9999

TABLE2

id            Col1  STRT_D       END_D

ABCD123  AA  05/01/2001 21/01/2001

ABCD123  BB  22/01/2001 02/03/2005

TABLE3

id            Col1  STRT_D       END_D

ABCD123  X1  01/01/2005 10/03/2005

ABCD123  X2  11/03/2005 31/12/9999

FINAL TABLE

ID         COL1_T1     COL1_T2   COL1_T3            STRT_D                 END_D

ABCD123   01          NULL            NULL            01/01/2001            04/01/2001

ABCD123   01            AA              NULL            05/01/2001            11/01/2001

ABCD123   02            AA              NULL            12/01/2001            21/01/2001

ABCD123   02            BB             NULL             22/01/2001            31/12/2004

ABCD123   02            BB             X1                 01/01/2005            02/03/2005

ABCD123   02            NULL         X1                 03/03/2005            10/03/2005

ABCD123   02            NULL         X2                 11/03/2005            31/12/9999

Considering the huge record count, I can't join to a calendar table to generate records for each day,

Apprecaite any help.

1 REPLY
Enthusiast

Re: Merge History tables and alligning the time periods

Hi Suresh,

It's difficult to understand how you're building the final table from the example you posted, but from what you've described, I advise you to spend some time familiarizing yourself with the period data type.  It has some very nice functionality with regard to overlapping time periods etc...

You can find the documentation online at http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1143_111A/Peri...

The documentation for the operators is just below the link above on the same page.

Cheers!