CODEWITHSUNDEEP
timestamprowteradatasubtractioncol
ASuit
ASuit

Reputation: 145

Subtracting time stamps when multiple column and row value changes

I have this data set. Columns 1,2,3,4 and 5. I want to subtract time between consequent transactions only when I see transaction type "A1" . First it should check whether for same ID, indicator has changed. If it has, then I want subtraction between A1 of indicator 1 and A1 of Indicator 2. If indicator has not changed then look at ID and see if that has changed. If it has, then find the immediate next A1 and subtract the times.

I want to display result in a new column as shown above (TRANSACTION TIME, in minutes). And, I am using Teradata SQL Assistant.

enter image description here

Upvotes: 0

Views: 66

Answers (1)

hhoeck
hhoeck

Reputation: 371

to be honest, I don't get the scenario. - What is the key (the columns) , where next TXN is searched? Do do search for next or last TXN?

Never mind:

1) The last / preceding record can be find via OLAP functions and just use MIN/MAX when using 1 record as window

MAX(COL1) OVER (PARTITION BY COL_Key1, COL_Key2 ORDER BY COL_Timestamp ASC ROWS 1 PRECEDING) as COL1_Preceding

check: http://www.info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/Ordered_Analytical_Functions_stub.html

2) the if then can be done via CASE WHEN xxx THEN xxx [WHEN yyy THEN yyy][ELSE zzz] END

check: http://www.info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/Case_Expressions_stub.html#ww3_28_1

Upvotes: 1

Related Questions