All Forums Database
S_GALILEO 8 posts Joined 12/15
07 Mar 2016
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.

 

Adeel Chaudhry 773 posts Joined 04/08
07 Mar 2016

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

-- If you are stuck at something .... consider it an opportunity to think anew.

S_GALILEO 8 posts Joined 12/15
08 Mar 2016

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

Kbos 20 posts Joined 04/13
11 Mar 2016

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

You must sign in to leave a comment.