Archives of the TeradataForum

Message Posted: Wed, 25 May 2011 @ 15:59:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Alan,

I'm not sure some of the prior responses will be exactly what you want. You definitely can't partition on field1/2/3 and take min/max start/end dates if values change back to a prior set.

Meher describes what you're looking for, this SQL should do it for you (assuming that field1 is the ID you're building your SCD around):

     select field1,
     field2,
     field3,
     start_dt,
     coalesce(min(start_dt - 1) over (partition by field1 order by start_dt
     rows between 1 following and 1 following),
              cast('9999/12/31' as date)) end_dt
     (
     select field1,
     field2,
     field3,
     start_dt,
     end_dt
     from mytable
     qualify field2 <> max(field2) over (partition by field1 order by
     start_dt rows between 1 preceding and 1 preceding)
          or field3 <> max(field3) over (partition by field1 order by
     start_dt rows between 1 preceding and 1 preceding)
          or max(start_dt) over (partition by field1 order by start_dt rows
     between 1 preceding and 1 preceding) is null
     ) changed_rows;

The qualify in the inner SQL picks out rows where field2 or field3 has changed or the row is the first for that value in field1 and is basically the same as Meher's skip flag. (If field2 or field3 is not nullable, then you could use it place of start_dt, which might save some performance.) In the outer SQL, we change end_dt into the day before the start date of the next row or 12/31/9999 if there is no next row (implementing Meher's start_dt - 1).

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>