Selecting the previuos row in a table.

Database
Teradata Employee

Selecting the previuos row in a table.

Hi,

I have a table with following attributes:
1. Acct_Num (Decimal 10,0) Not Null
2. Prod_Code (Char 5)
3. Start_Date (Date format 'yyyy-mm-dd')

The index is Acct_Num & not UPI.

The table is already present with about 60 million rows & now I am adding up to new columns as Prev_Code & Prev_Code change date initially poupulating them as NULL. I have to update all the historic rows. Here is an interesting scenario:

Acct_Num Product_Code Start_Date Prev_Code Prev_Code_change_date
--------- ------------- ----------- --------- ---------------------
11111 123 30/11/2005 NULL NULL
11111 345 01/09/2006 NULL NULL
11111 345 01/01/2007 NULL NULL
11111 123 30/01/2007 NULL NULL

I have to update the new columns if there is a product code change else the values should remain as the prv code.
Result expected:

First Row - (Prev_Code = NULL, Prev_Code_change_date = NULL) - no prod code change
Second Row - (Prev_Code = 123, Prev_Code_change_date = 01/09/2006) - prod code change
Third Row - (Prev_Code = 123, Prev_Code_change_date = 01/09/2006) - no change
Fourth Row - (Prev_Code = 345, Prev_Code_change_date = 30/01/2007) - prod code change

How shall i compare a row (considering start_date) with its previuos row?? or any other feasible solution in this case??

1 REPLY
Teradata Employee

Re: Selecting the previuos row in a table.

you can try somthing along the lines of emulating the lag function. It would be more or less along the lines of this example:

select * from foo a,
(select acct_num, product_code, start_date, max(start_date) over (partition by acct_num order by acct_num, start_date rows between 1 preceding and 1 preceding) as preceding_date from foo ) b
where
a.acct_num = b.acct_num
and a.start_date = b.preceding_date
and a.product_code <> b.product_code