Calculate column value based on previous computed value

Database
WAQ
Enthusiast

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.

5 REPLIES
WAQ
Enthusiast

Re: Calculate column value based on previous computed value

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

Re: Calculate column value based on previous computed value

You can do this as given below

SEL  VAL.*, FRU - CSUM(VEG,VEG DESC) AS TOTAL
FROM test VAL
WAQ
Enthusiast

Re: Calculate column value based on previous computed value

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

Enthusiast

Re: Calculate column value based on previous computed value

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
Enthusiast

Re: Calculate column value based on previous computed value

Thanks a lot, this is what I was looking for.