All Forums Analytics
aamer123 1 post Joined 01/15
08 Jan 2015
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 .. 

dnoeth 4628 posts Joined 11/04
09 Jan 2015

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

 

Dieter

Raja_KT 1246 posts Joined 07/09
09 Jan 2015

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
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.