Merge History tables and alligning the time periods

Database
Not applicable

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.

6 REPLIES
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!

Re: Merge History tables and alligning the time periods

Hello ,

I'm not able to find the link .. I'm also looking for the same answer , could you please  provide any info

Teradata Employee

Re: Merge History tables and alligning the time periods

With temporal option, I would do it following this logic.

Note from author's code I changed the end_date because with period datatype, the end date is excluded vs included in the example provided.

 

Data

create multiset volatile table table1, no log
( t_id      char(7)
, col1      char(2)
, start_d   date        format 'yyyy-mm-dd' not null
, end_d     date        format 'yyyy-mm-dd' not null compress (date '9999-12-31')
, period for pd_valid (start_d, end_d) as VALIDTIME
)
primary index (t_id)
on commit preserve rows;

create multiset volatile table table2, no log as table1 with no data on commit preserve rows;
create multiset volatile table table3, no log as table1 with no data on commit preserve rows;

nonsequenced validtime insert into table1 (t_id, col1, start_d, end_d) values ('ABCD123', '01', date '2001-01-01', date '2001-01-12');
nonsequenced validtime insert into table1 (t_id, col1, start_d, end_d) values ('ABCD123', '02', date '2001-01-12', date '9999-12-31');

nonsequenced validtime insert into table2 (t_id, col1, start_d, end_d) values ('ABCD123', 'AA', date '2001-01-05', date '2001-01-22');
nonsequenced validtime insert into table2 (t_id, col1, start_d, end_d) values ('ABCD123', 'BB', date '2001-01-22', date '2005-03-03');

nonsequenced validtime insert into table3 (t_id, col1, start_d, end_d) values ('ABCD123', 'X1', date '2005-01-01', date '2005-03-11');
nonsequenced validtime insert into table3 (t_id, col1, start_d, end_d) values ('ABCD123', 'X2', date '2005-03-11', date '9999-12-31');

Creating a result table then feeding it

create multiset volatile table mvt_result, no log
( t_id      char(7)
, col1_t1   char(2)
, col1_t2   char(2)
, col1_t3   char(2)
, start_d   date        format 'yyyy-mm-dd' not null
, end_d     date        format 'yyyy-mm-dd' not null compress (date '9999-12-31')
, period for pd_valid (start_d, end_d) as VALIDTIME
)
primary index (t_id)
on commit preserve rows;

nonsequenced validtime
insert into mvt_result (t_id, col1_t1, start_d, end_d)
select t_id, col1, start_d, end_d
from table1; sequenced validtime update mvt_result from table2 set col1_t2 = table2.col1 where mvt_result.t_id = table2.t_id; sequenced validtime update mvt_result from table3 set col1_t3 = table3.col1 where mvt_result.t_id = table3.t_id;

Retrieving the results

nonsequenced validtime
  select * from mvt_result
order by t_id, start_d;

 t_id           col1_t1 col1_t2 col1_t3 start_d    end_d
 -------------- ------- ------- ------- ---------- ---------- 
 ABCD123        01      NULL    NULL    2001-01-01 2001-01-05
 ABCD123        01      AA      NULL    2001-01-05 2001-01-12
 ABCD123        02      AA      NULL    2001-01-12 2001-01-22
 ABCD123        02      BB      NULL    2001-01-22 2005-01-01
 ABCD123        02      BB      X1      2005-01-01 2005-03-03
 ABCD123        02      NULL    X1      2005-03-03 2005-03-11
 ABCD123        02      NULL    X2      2005-03-11 9999-12-31

Re: Merge History tables and alligning the time periods

Thanks Waldar for your quick respose! But I can not really peform an update since the table2 has many fields which has to be considered and it has more history data. Actually speaking my requirement is I have one Policy table which has got independent history handling(SCD Type 2 ) and another status table which has independent scd type 2 history handling. now I need to maintain one single Policy_status stable with all the hisotry in policy and status.  so in a way I have only two scd type 2 tables which having data loaded independently(unique Key Policy ID present) but the start_date and end_date different(Since both table loaded independently)

Re: Merge History tables and alligning the time periods

Hey all,

 

anyone could help me here please. I really stuck here on these. atleast any suggestions how these kind of scenarios can be handled

Teradata Employee

Re: Merge History tables and alligning the time periods

Hi Dhanraj8080,

 

Do you have the temporal activated on your database ?

 

In my post I didn't update any source tables.

I've created a volatile temporal table specifically to handle the scenario, populated it with base data then updated it to handle overlapsing periods.

Once it's updated, it's up to you to do what you want with it. On this forum I've done a regular select, but you can copy the data in another table for example.

 

About the documentation, please search for "Period Data Type" in https://docs.teradata.com, then choose your DB version.

For 16.20 it would be : https://docs.teradata.com/reader/~_sY_PYVxZzTnqKq45UXkQ/7qWnQ_eWHbehoqicCCFtXA