Archives of the TeradataForum

Message Posted: Fri, 04 Jan 2008 @ 18:05:26 GMT

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

You could do it two steps. This assumes that the staging table only ever has rows to be added to the chronological end of the target table, so you're not splitting existing time spans in the target.

1) Update the latest target table row with the start time - 1 second of the earliest staging table row for each entity, roughly:

     Update target
     Set start_time = (select min(start_time) - interval '1' second from
     staging where target.id = staging.id)
     Where exists (select 1 from staging where target.id = staging.id);

(Not necessarily the best SQL, but gets the point across.)

2) Insert from the staging table using following rows to set the end_time for each row. This uses 12/31/9999 as the end_time of the current row, just a guess on my part:

     Insert into target (id, start_time, end_time, field1, field2 etc.)
     Select (id,
          start_time,
              case when end_time is null
                   then cast('9999/12/31 23:59:59' as timestamp)
                   else end_time end end_time,
              field1,
              field2 (etc.)
     From (
     Select id,
             start_time,
             min(start_time) over (partition by id order by start_time rows
     between 1 following and 1 following) end_time,
             field1,
             field2 (etc.)
     From staging) x

(You could put the CASE in the inner query, but this makes it easier to see what's happening.)

Best of luck,

Dave

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