Temporal usage classic scenerio

Database
Teradata Employee

Temporal usage classic scenerio

Hi

I need to track history of party addresses and created a temporal table for this.

CREATE MULTISET TABLE Employee_Addr_Hist (

Name varchar(100),

City varchar (100),

VT PERIOD(DATE) NOT NULL 

)

PRIMARY INDEX(name);

insert into Employee_Addr_Hist (name,city, Validity) values ( 'John','London' ,period( date '2011-01-01', until_changed) );

Now today(2013-03-19) I received a new row from source saying 'John' moved to 'Paris' on '2012-01-01'.

If I use below Update, CURRENT_DATE is used to close previous record and open new. Which is wrong as John moved to Paris on 2012-01-01.

update Employee_Addr_Hist

set  City = 'Paris'

where name = 'John'

and end(VT)  is until_changed

Can anyone help me in this? I just need to use source date for closing old record and opening new using Temporal features.

Tags (1)
2 REPLIES

Re: Temporal usage classic scenerio

You can add your SET statement like below -

SET VT = PERIOD(BEGIN(VT), source_date_column)

Re: Temporal usage classic scenerio

Hello i tried this but is not working. It doesnt allow me to use this on my update stament. Any other suggestion?