Teradata Query to handle previous day amount

Database

Teradata Query to handle previous day amount

Hi All,

Need your help on the below scenario.

I have a table as below and the below calculation needs to be preformed.

ID    Date                   Amount    calculation

1   26-Jun-2015          100         100(100+Previous day amount)

1   27-jun-2015           25           125(25+previous day amount)

1   28-jun-2015          80             105(80+previous day amount)

Please help me out with the query to calculation this values.

Thanks,

Hajeera

3 REPLIES
Enthusiast

Re: Teradata Query to handle previous day amount

Hi,

Kindly let me know that the below query fits in your requirement:

select ID,PAY_DATE,AMOUNT,PREVIOUS_AMT

from

(select  ID,PAY_DATE,AMOUNT,(AMOUNT+coalesce(max(AMOUNT)over(order by ID rows between  1 preceding and 1 preceding),0)) as PREVIOUS_AMT

 from CALCULATION) as seq1

Re: Teradata Query to handle previous day amount

Thank you for your reply :)

But unfortunatly it did not work.. This just takes the previous amount of any record in my answerset. Not based on Date and unique_ids.

Junior Contributor

Re: Teradata Query to handle previous day amount

select ID, Date, Amount,
Amount + coalesce(max(amount)
over (partition by ID
order by Date
rows between 1 preceding and 1 preceding), 0)
from tab