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
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';