Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Database WAQ 158 posts Joined 02/10 10 Jul 2016 Calculate column value based on previous computed value Hi, I have a requirement to make a derived column called TOTAL which is calculated by subtracting FRU from VEG column (based on grouping of org and dest) and then for every next row calculate the value by subtracting the last value of TOTAL with the current row VEG value and so on. So the result would be something like this: Before any calculation org        dest    FRU        VEG APP        PKR        50        16 APP        PKR        50        15 APP        PKR        50        10 APP        PKR        50        9 After calculation org        dest    FRU        VEG        TOTAL APP        PKR        50        16        34 APP        PKR        34        15        19 APP        PKR        19        10        9 APP        PKR        9        9        0 Any help would be highly appreciated.   WAQ 158 posts Joined 02/10 10 Jul 2016 Here are DDL's and DML's for the above example: ```drop table test; create volatile table test ( org varchar(5) ,dest varchar(5) ,FRU integer , VEG integer ) on commit preserve rows; insert into test values ('APP', 'PKR', 50, 10); insert into test values ('APP', 'PKR', 50, 15); insert into test values ('APP', 'PKR', 50, 9); insert into test values ('APP', 'PKR', 50, 16);```  sachin.s...ebastian 54 posts Joined 05/09 12 Jul 2016 You can do this as given below ```SEL VAL.*, FRU - CSUM(VEG,VEG DESC) AS TOTAL FROM test VAL```  WAQ 158 posts Joined 02/10 13 Jul 2016 Hi Sachin, Thanks for you response. Can we do the same without using CSUM as it is a deprecated function. Also in the output report, I want to display the previous row TOTAL value in the FRU column as shown in the table "After Calculation". So the output in the FRU column would be: 50 34 19 9 sachin.s...ebastian 54 posts Joined 05/09 13 Jul 2016 Here you go ```SEL ORG, DEST ,COALESCE (FRU-SUM(VEG) OVER (ORDER BY VEG DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), FRU) AS FRU ,VEG ,FRU - SUM(VEG) OVER (ORDER BY VEG DESC ROWS UNBOUNDED PRECEDING ) AS TOTAL FROM test ```  WAQ 158 posts Joined 02/10 18 Jul 2016 Thanks a lot, this is what I was looking for. You must sign in to leave a comment. Active Posters