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

Database
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
Teradata Employee

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.

Teradata Employee

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

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_data
group by 1

select
cse_id,
sum(case when tx_typ_cd =120 then -1 else 1 end * tx_amt)
from raw_data A
where (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