Database
Highlighted
Enthusiast

## perform plus or minus calcultions on multiple records based on their transaction code

Hi there,

I need some help on combining multiple transaction records in a table with same case id into one case. that is transaction code 101, then the amount present original amount, when transaction code is 120, then that amount needs to be deducted from original amount, when the code is 103, that amount needs to be added to the results. a sample is shown in below pic. Many thanx.

4 REPLIES 4

## Re: perform plus or minus calcultions on multiple records based on their transaction code

`select  cse_id,  sum(case when tx_typ_cd =120 then -1 else 1 end * tx_amt)from raw_data`
Enthusiast

## Re: perform plus or minus calcultions on multiple records based on their transaction code

thanx atardecerR0j0... it is quite late in australia, i will give it a try tomorrow.. thanx again!

Enthusiast

## Re: perform plus or minus calcultions on multiple records based on their transaction code

Hi atardecerR0j0... i have tried your code, it works perfectly.. thanx!!.. now it is abit more complicated...

as attached graph.. when there is code 101 exist, I will grab transaction amount in 101 to perform calculation, however, when there is 101 and 102 both exist, we are only pick transaction amount under 102, ignore 101. how can that be done? any suggestions? many thanx.

`create multiset volatile table raw_data(cse_id char(1),tx_typ_cd integer,tx_amt integer)primary index( cse_id )on commit preserve rows;insert into raw_data values( 'A', 101, -2850 );insert into raw_data values( 'A', 120,  2000 );insert into raw_data values( 'A', 103,  1730 );insert into raw_data values( 'B', 101,    59 );insert into raw_data values( 'B', 102,    79 );insert into raw_data values( 'B', 120,  -450 );insert into raw_data values( 'B', 103,   175 );select  cse_id,  sum(case when tx_typ_cd =120 then -1 else 1 end * tx_amt)from raw_datagroup by 1select   cse_id,  sum(case when tx_typ_cd =120 then -1 else 1 end * tx_amt)from raw_data Awhere (cse_id, tx_typ_cd) not in(  select cse_id, 101  from raw_data  group by cse_id  having count(case when tx_typ_cd=101 then 1 else null end)>0    and count(case when tx_typ_cd=102 then 1 else null end)>0 )group by 1`