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

 

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

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.