Need logic in teradata (SCD TYPE 2)

General
Enthusiast

Need logic in teradata (SCD TYPE 2)

Need logic in teradata

Hi Team,

please find the sample records below , I  need a logic for the below records ...

when ever New record comes .. CNTRCT_SEQ_NBR will be increased by 1 in the input  and i need logic that,  max(EFF_END_DT) should be 12/31/9999 .i need to update M_SYS_DT to EFF_STRT_DT  and i need to update the EFF_END_DT  date which Max (CNTRCT_SEQ_NBR)-1 ..... In simple words its SCD type 2

INPUT

CNTRCT_NBR       CNTRCT_SEQ_NBR     EFF_STRT_DT    EFF_END_DT        M_SYS_DT

201-0005898-005         1             7/22/2009        12/31/9999        7/22/2009

OUTPUT    

CNTRCT_NBR            CNTRCT_SEQ_NBR        EFF_STRT_DT        EFF_END_DT        M_SYS_DT

201-0005898-005            1                7/22/2009        12/31/9999         7/22/2009

IF NEW RECORDS COMES

INPUT                

CNTRCT_NBR        CNTRCT_SEQ_NBR        EFF_STRT_DT        EFF_END_DT        M_SYS_DT

201-0005898-005        2                11/2/2009        12/31/9999        11/2/2009

201-0005898-005        1                11/2/2009        12/31/9999        7/22/2009

OUTPUT                

CNTRCT_NBR            CNTRCT_SEQ_NBR            EFF_STRT_DT        EFF_END_DT        M_SYS_DT

201-0005898-005        2                        11/2/2009        12/31/9999        11/2/2009

201-0005898-005        1                        7/22/2009        11/2/2009        7/22/2009

1 REPLY
Enthusiast

Re: Need logic in teradata (SCD TYPE 2)

Hope you already had the logic to find the delta (change capture process) by comparing two versions of data and derive the indicator either 'I' (For new inserts), 'U' (for updates), 'D' (for deletes). After that, you can try below skelton of SQLs -

insert into finaltable  select  col1,col2....date, '12-31-9999',sysdate from deltatable where indictor in ('I', 'U');

update finaltable set eff_end_dt= date -1 where finaltable.keycol=deltatable.keycol and deltatable.indicator in ('U','D');