Date as Integer

Database
Junior Contributor

Re: Date as Integer

If you seriously want to implement SCD you need the right to create SPs.

The code will mostly be generic, so as a basis you can simply use existing code from any RDBMS.

Or use Teradata's Temporal feature in TD13.10, which is exactly what you're looking for.

Enthusiast

Re: Date as Integer

I dont have access to the SP's and i am on TD12, so decided to write a series of Insert and update statements to tackle the type2 objects.

Source Table A

Target Table B, i want to update this table based on table A, i want to keep the history so added Effective_Date_From/To to the target table if the values changes in a column.

Written a few insert and update statements, but the target table is not updated...am i missing something here??

Insert INTO B

select

ST.Client_ID,

ST.Business,

ST.Segment,

Current_Date,

NULL

from A ST

LEFT OUTER JOIN B ED

ON ST.Client_ID=ED.ClientID_PK

AND

ED.Effective_Date_To IS NULL

where ED.ClientID_PK IS NULL;

UPDATE B

FROM A ST

SET Effective_Date_To= Current_Date

where ST.Client_ID = B.ClientID_PK

and B.Effective_Date_To IS NULL;

Insert INTO B

select

ST.Client_ID,

ST.Business,

ST.Segment,

Current_Date,

NULL

from A ST

LEFT OUTER JOIN B ED

ON ST.Client_ID=ED.ClientID_PK

AND

ED.Effective_Date_To IS NULL

where ED.ClientID_PK IS NULL;
Enthusiast

Re: Date as Integer

what are the columns that you think are changing in the table B versus A, you need to mention them as <> conditions in your update like

Update B

from A ST

Set Efftctive_Date_tb=current_date

where st.client_id=B.clienid_pk and

(ST.business <>B.business or ST.segment<>B.segment)

and B.effective_date_to is nUll;

This implies that only if there is a change in the values the old value will be updated else not.

Enthusiast

Re: Date as Integer

I am trying to change both Business and segment as these are type 2 fields in the table.

So this update statement needs to be added after the above code i have provided?

Enthusiast

Re: Date as Integer

Hi,

The Update Statement followed by an insert statement will work.

Update B
from A ST
Set Efftctive_Date_tb=current_date
where st.client_id=B.clienid_pk and
(ST.business <>B.business or ST.segment<>B.segment)
and B.effective_date_to is nUll;

Insert INTO B   
select  ST.Client_ID, 
 ST.Business,  
ST.Segment,  
Current_Date,  
NULL 
from
A ST 
 LEFT OUTER JOIN B ED 
 ON ST.Client_ID=ED.ClientID_PK  
AND  ED.Effective_Date_To IS NULL 
where ED.ClientID_PK IS NULL;

Thanks & Regards,

Adharssh.

Enthusiast

Re: Date as Integer

Team,

i need help in writing a stored proc for adding integer to a date column by excluding weekends & custom holidays.

For example : OE_DATE + DAYS = Calculated Field ( Date )

                       02/27/15 + 1  = 03/02/15 ( 02/28 & 3/1 are weekends )

                       02/27/15 + 1  = 03/03/15 ( 02/28, 3/1 are weekends & 3/2 is a holiday )