All Forums General
vinay4125 6 posts Joined 03/15
02 Jan 2016
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

sgarlapa 88 posts Joined 03/13
02 Jan 2016

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');
 
 
 

You must sign in to leave a comment.