create SDC from a history table

Analytics

create SDC from a history table

Please help me convert the data FROM table to To table

FROM: 

Col1   Start       End   Status
S1 1 5 Y
S1 6 10 Y
S1 11 15 Y
S1 16 20 N
S1 21 25 N
S2 1 5 N
S2 6 10 Y
S2 11 15 Y
S2 16 20 Y

TO: 

Col1   Start       End   Status
S1 1 15 Y
S1 16 25 N
S2 1 5 N
S2 6 20 Y

Thank you .. 

2 REPLIES
Highlighted
Junior Contributor

Re: create SDC from a history table

Combine rows if there's no gap and the status didn't change?

SELECT
col1
,start
,COALESCE(MIN(prevEnd) -- next rows end
OVER (PARTITION BY col1
ORDER BY start
ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING)
,maxEnd) -- or max end
,status
FROM
(
SELECT
col1, start, end, status
​ -- previous rows end
,MAX(end_) OVER (PARTITION BY col1 ORDER BY start ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prevEnd
-- max end
,MAX(end_) OVER (PARTITION BY col1) AS maxEnd
FROM vt
QUALIFY -- return gaps
prevEnd + 1 < start
-- or 1st row
OR prevEnd IS NULL
-- or changed status
OR Status <> MAX(status) OVER (PARTITION BY col1 ORDER BY start ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
) AS dt
Enthusiast

Re: create SDC from a history table

aamer123,

You can check with this.... if it helps.

select * from

(select col1,min(start1)over(partition by col1,stat1 order by  col1,stat1) st,

max(end1) over(partition by col1,stat1 order by  col1,stat1) ed, stat1 from your_table

) dt

group by 1,2,3,4 order by 1,2