How to update the current row value based on the previous row value of the same column

Database
Enthusiast

How to update the current row value based on the previous row value of the same column

 

Dear all, 

I have the following data:

DATE      CODE RANK
? ABS 0
12/04/2014 RET 0
20/04/2014 STT 0
01/05/2014 RETk 0
13/05/2014 RETj 0


RANK is the column I want to calculate in my SQL given the columns DATE and CODE. It's initialized here to 0. 

The logic i want to implement is as follows:

   If date = '?'  (that is the first line)

      If  CODE = 'ABS' THEN 0 ELSE 1

   Else 

     If RANK - 1 (previous RANK value)  = 1 AND CODE = RET THEN RANK = 2

   Else 0

I cannot figure out how to update the current column value given the previous one... I tried many logic implementation with OLAP functions without success. 

Can anyone give me a hint? 

Regards.




Tags (1)
3 REPLIES
Teradata Employee

Re: How to update the current row value based on the previous row value of the same column

You can use self-join using derived SQL. Once set with RANK and other set with RANK - 1.

Enthusiast

Re: How to update the current row value based on the previous row value of the same column

Thank you very much, 

Can you please elaborate more about the solution? Even with self join, how can I use the column that has not been created yet in the self-join, that is the RANK column in this example? 

Regards

Enthusiast

Re: How to update the current row value based on the previous row value of the same column

You may try to enumarate every single row. When you are done, try to group by the criterias you decide, so that's one way you can control what the preview row is and what the next one is.

DATE             CODE ROW_N
12-10-2014 ABS0 1
12-11-2014 ABS0 2
11-10-2014 ABS0 3
12-10-2014 STT 1

Regards