Archives of the TeradataForum

Message Posted: Thu, 20 Dec 2012 @ 20:03:56 GMT

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

Hi,

I need to get the previous and next action of the current action (row).I am able to achieve this is in oracle by the below logic,

     LEAD(action_name,1) OVER(PARTITION BY ID,request_name ORDER BY
     timestamp_det) next_action

     LAG(action_name,1) OVER(PARTITION BY ID,request_name ORDER BY timestamp_det)
     previous_action

Please let me know how to implement the same in Teradata.

Note: Find the sample input and expected output details for your reference.

Input:

     id   request_name          timestamp_det                     action_name
     10   account open          01-OCT-12 09.00.31.895478000 AM   submit
     10   account open          01-OCT-12 09.00.31.897796000 AM   valideta
     10   registration level1   01-OCT-12 09.00.31.932583000 AM   submit
     10   registration level1   01-OCT-12 11.53.28.460491000 AM   chkout
     10   registration level1   01-OCT-12 11.54.18.182842000 AM   approve
     10   registration level1   01-OCT-12 11.54.18.184683000 AM   chkin
     10   account approve       01-OCT-12 11.54.18.337172000 AM   comple

Output:

     id   previous_action   Current action   next_action
     10                     submit           validate
     10   submit            valideta
     10                     submit           chkout
     10   submit            chkout           approve
     10   chkout            approve          chkin
     10   approve           chkin
     10                     comple

Regards,

Sd

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