CODEWITHSUNDEEP
rowteradatalag
ckp
ckp

Reputation: 593

How to get previous row values Teradata

I have the data in the following format

 Id    Code    Date         Amount       Type
 101   B25     5/4/2020      $500         C
 101   A15     5/5/2020      $100         D
 101   D15     5/5/2020      $200         D
 102   B35     6/2/2020      $400         C
 102   A15     6/2/2020      $50          D

I need the following

 Id    Code    Date         Amount       Type  C_Date          C_Amount
 101   A15     5/5/2020      $100         D    5/4/2020         $500
 102   A15     6/2/2020      $50          D    6/2/2020         $400

For all Code='A15' I need Date and amount from previous row where Type='C'

I did this

 Select id, Amount, Date,
  sum(Amount) over (partition by ID ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as 
   C_Amount,
  Max(Date) over (partition by ID ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as 
   C_Date
 from Table
 where code='A15' or Type='C'

Output is not the desired one

 Id    Code    Date         Amount       Type  C_Date          C_Amount
 101   A15     5/5/2020      $100         D    ***5/5/2020         $100***
 102   A15     6/2/2020      $50          D    ***6/2/2020         $50***

Any help is appreciated

Upvotes: 0

Views: 4190

Answers (1)

dnoeth
dnoeth

Reputation: 60462

The answer set doesn't match your query, it will include 'C' rows, too.

ROWS between UNBOUNDED PRECEDING and CURRENT ROW is a group sum.

You need ROWS between 1 PRECEDING and 1 PRECEDNG and an ORDER BY to get the previous row's value.

Better switch to LAG or LAST_VALUE, which is simpler and allows dealing with additional rows between the 'A15' and the previous 'C' row:

 Select id, Amount, Date,
  LAG(case when Type='C' then Amount end IGNORE NULLS)
  over (partition by ID
        order by date) as C_Amount,
  LAG(case when Type='C' then date end IGNORE NULLS)
  over (partition by ID
        order by date) as C_Date
 from Table
 where code='A15' or Type='C'
 qualify code='A15';

Upvotes: 2

Related Questions