Archives of the TeradataForum

Message Posted: Wed, 25 May 2011 @ 11:23:41 GMT

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

Hi,

We need two step process as below to build as per your requirement.

Step 1: Skip records

Add a new column to skip records and set to 'Y' (Skip_Flag). Need to use rownumber function here to compare 1st record with 2nd record.

     field1   field2   field3   field4   start_date   end_date     Skip_Flag
     ------   ------   ------   ------   ----------   --------     ---------
     1001     AAA      value3            01/01/2011   10/01/2011   N
     1001     AAA      value3   555      11/01/2011   22/01/2011   Y
     1001     AAA      value3   11       23/01/2011   10/03/2011   Y
     1001     BBB      value3   11       11/03/2011   08/05/2011   N
     1001     AAA      value3   11       09/05/2011   31/12/2999   N

Step 2: Rebuild historical dates (Need to generate rownumbers using OLAP row_number() over(partition by field1 order by start_date))

Note: Not using field2 in partition by clause as your example shows building history though field2 is different.

     field1   field2   field3   field4   start_date   end_date     Skip_Flag   Rnum
     ------   ------   ------   ------   ----------   --------     ---------   -----
     1001     AAA      value3            01/01/2011   10/01/2011   N           1
     1001     BBB      value3   11       11/03/2011   08/05/2011   N           2
     1001     AAA      value3   11       09/05/2011   31/12/2999   N           3

Start_date -1 should be used to rebuild dates.

Thanks

Meher

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