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.

8 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

Teradata Employee

Re: Merge History tables and alligning the time periods

Hi,

OK, I like having delete records; once an ID has appeared, there is always an active record for it, even if it has the delete flag set.

So my solution will fail to spot the last end-date on table 2.  This can be fixed, but probably adds complexity.

End-dates are then just derived data - you can calculate the end-dates as just the (start date of next record) -1.

Now, the output data can only change on dates recorded as start date in one of the input tables.
So, I first create a list of unique start_dates from the input tables.  Then I join the data on just those dates.  Then I fill in the end-dates.

create multiset volatile table mytable1, 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 
)
primary index (t_id)
on commit preserve rows;

create multiset volatile table mytable2, no log as table1 with no data on commit preserve rows;
create multiset volatile table mytable3, no log as table1 with no data on commit preserve rows;
insert into mytable1 (t_id, col1, start_d, end_d) values ('ABCD123', '01', date '2001-01-01', date '2001-01-11');
insert into mytable1 (t_id, col1, start_d, end_d) values ('ABCD123', '02', date '2001-01-12', date '9999-12-31');
			
insert into mytable2 (t_id, col1, start_d, end_d) values ('ABCD123', 'AA', date '2001-01-05', date '2001-01-21');
insert into mytable2 (t_id, col1, start_d, end_d) values ('ABCD123', 'BB', date '2001-01-22', date '2005-03-03');
		
insert into mytable3 (t_id, col1, start_d, end_d) values ('ABCD123', 'X1', date '2005-01-01', date '2005-03-10');
insert into mytable3 (t_id, col1, start_d, end_d) values ('ABCD123', 'X2', date '2005-03-11', date '9999-12-31');

create volatile table myDates,no log 
( t_id      char(7)
, start_d   date        format 'yyyy-mm-dd' not null
) primary index (t_id) on commit preserve rows;

insert into myDates 
       select t_id, start_d from mytable1
union  select t_id, start_d from mytable2
union  select t_id, start_d from mytable3;

create multiset volatile table myresult, 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' 
)
primary index (t_id)
on commit preserve rows;
create multiset volatile table mytemp, no log as myresult with no data on commit preserve rows;

insert into mytemp
select d.t_id,
t1.col1 as col1_t1,
t2.col1 as col1_t2,
t3.col1 as col1_t3,
d.start_d,
null as end_d
from  myDates d
left outer join mytable1 t1 on d.t_id = t1.t_id and d.start_d between t1.start_d and t1.end_d
left outer join mytable2 t2 on d.t_id = t2.t_id and d.start_d between t2.start_d and t2.end_d
left outer join mytable3 t3 on d.t_id = t3.t_id and d.start_d between t3.start_d and t3.end_d
;

insert into myresult 
select t_id,
col1_t1,
col1_t2,
col1_t3,
start_d,
coalesce((max(start_d) over (partition by t_id order by start_d asc rows between 1 following and 1 following))-1,date '9999-12-31') as end_d
from mytemp;

select * from myresult
order by t_id, start_d;

There are tweaks to improve performance, but I think the logic is sound.

Hope this helps,  do let me know how you get on,
Tony

Teradata Employee

Re: Merge History tables and alligning the time periods

As I walked away, I realised how to handle tables without records with high value end dates (eg. table2).  Add something like below into the insert into the dates table:

union  select t_id, end_d+1 from mytable1 where end_d <> date '9999-12-31'
union  select t_id, end_d+1 from mytable2 where end_d <> date '9999-12-31'
union  select t_id, end_d+1 from mytable3 where end_d <> date '9999-12-31'

If the destination is a set table, you can replace union with union all, which will probably be quicker.

Cheers,

Tony