General

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-05-2018
10:41 AM

11-05-2018
10:41 AM

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

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-05-2018
01:33 PM

11-05-2018
01:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-05-2018
01:33 PM

11-05-2018
01:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-05-2018
03:10 PM

11-05-2018
03:10 PM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-05-2018
03:24 PM

11-05-2018
03:24 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-06-2018
01:18 AM

11-06-2018
01:18 AM

Re: SCD2 tabe comprression

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-08-2018
07:56 AM

11-08-2018
07:56 AM

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.