All Forums Database
hajeera 2 posts Joined 09/15
08 Sep 2015
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

saravanatn 10 posts Joined 07/11
09 Sep 2015

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
 
 
 

hajeera 2 posts Joined 09/15
09 Sep 2015

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.

dnoeth 4628 posts Joined 11/04
10 Sep 2015
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

 

Dieter

You must sign in to leave a comment.