General
Highlighted
Fan

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

Accepted Solutions
Junior Contributor

## Re: SCD2 tabe comprression

```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

```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```

## 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 :-)