SCD2 tabe comprression

General

SCD2 tabe comprression

Hello experts,

 

I do have one SCD type 2 table but I need to extract data based on 2 columns (Column 1 and Column 2 ) and derive their Eff_from_date and eff_to_dt. Consider the below pic for the Input data and Output required.  

 

I used below Query but eff_to_dt comes - 31-dec-9999 for all column2 which is repeted  

SEL
COLUMN1,COLUMN2 , EFF_FROM_DT , MAX(EFF_TO_DT) over (partition by COLUMN1,COLUMN2 order by eff_froM_dt rows between 1 preceding and 1 preceding);
FROM TABLE_NAME
qualify COLUMN2 <>max(COLUMN2) over (partition by COLUMN1 order by eff_from_dt rows between 1 preceding and 1 preceding);

 
scd2.JPG


Accepted Solutions
Junior Contributor

Re: SCD2 tabe comprression

That's a task for NORMALIZE:

SELECT COLUMN1,COLUMN2
  ,Begin(pd) +1 AS EFF_FROM_DT -- add a day back to match your logic
  ,End(pd) AS EFF_TO_DT
FROM 
 (
   SELECT NORMALIZE  -- works with Periods only
      COLUMN1,COLUMN2
     ,PERIOD(EFF_FROM_DT -1, EFF_TO_DT) AS pd -- need subtract 1 day because Periods include the start but exclude the end
   FROM tab
 ) AS dt
1 ACCEPTED SOLUTION
5 REPLIES
Junior Contributor

Re: SCD2 tabe comprression

That's a task for NORMALIZE:

SELECT COLUMN1,COLUMN2
  ,Begin(pd) +1 AS EFF_FROM_DT -- add a day back to match your logic
  ,End(pd) AS EFF_TO_DT
FROM 
 (
   SELECT NORMALIZE  -- works with Periods only
      COLUMN1,COLUMN2
     ,PERIOD(EFF_FROM_DT -1, EFF_TO_DT) AS pd -- need subtract 1 day because Periods include the start but exclude the end
   FROM tab
 ) AS dt
Teradata Employee

Re: SCD2 tabe comprression

I think the +1 / -1 is in the end part of the period ;)

SELECT COLUMN1
, COLUMN2 , Begin(pd) AS EFF_FROM_DT , End(pd) - 1 AS EFF_TO_DT -- substract a day back to match your logic FROM (SELECT NORMALIZE -- works with Periods only COLUMN1
, COLUMN2 , PERIOD(EFF_FROM_DT, EFF_TO_DT + 1) AS pd -- need add1 day because Periods include the start but exclude the end FROM tab) AS dt
Junior Contributor

Re: SCD2 tabe comprression

Yes, I know, this can be used, too. In fact it simplifies the End(pd)-1 calculation to Last(pd) AS EFF_TO_DT

 

But the original data seems to have 9999-12-31 as eternity and then your initial EFF_TO_DT + 1 will fail :-)

 

Highlighted
Teradata Employee

Re: SCD2 tabe comprression

Oh yes, I overlooked the 9999 year. Good catch (as always) !

Re: SCD2 tabe comprression

Apology for the delay as I was on vacation. I just tested and saw its working fine. Many thanks, @dnoeth! It helps.