calculate cumulative subtraction

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Fan

calculate cumulative subtraction

I have a data set where i need to distribute the amount of col1 into col2 and i need to calculate the columns "new amount col2" and "New amount col1"

 

col1col2amount col1divisiontypeenew amount col2New amount col1
KS13 KS131,907.690.60000percent1144.6141144.614
KS13CS21,907.690.40000percent763.0761144.614
KS13CS31,907.69100fixed1001044.614
KS13CS41,907.691,000fixed100044.614
CC1CCS1,644.321,200fixed1200444,32

...

SyntaxEditor Code Snippet

 SELECT  col1,col2,amountcol1,division, SUM(
 CASE 
 WHEN typee='percent' THEN amountcol1*division
 WHEN typee ='fixed'  THEN division
 END)  AS newamountcol2  FROM  table1
 

 now i cant figure out how to calculate "Newamountcol1"

3 REPLIES
Junior Contributor

Re: calculate cumulative subtraction

I don't understand how you calculate Newamountcol1.

What's the relation between fixed & percent amounts regaring this cumulative sum?

And how is the order of rows defined?

Fan

Re: calculate cumulative subtraction

 
Junior Contributor

Re: calculate cumulative subtraction

You need an order to calculate a cumulative sum and if you want to be able to repeatedly get the same result it must be a unique sort order.