Calculated Fileds New User TDA - database sqlserver

Toad Data Point & Toad Intelligence Central
1

I have 2 columns in table= BUF_TRANSACTIONS
Cloumn 1 = Tran_Code
Contains transaction codes DP(Deposits) IE(interest Earned) WD (WithDrawl)
Column 2= Tran_Amount
Contains amounts for each Tran_Code

I want to add all tran_code DP+IE having Tran_Amount and Subtract WD to get a Balance.
Computation DP+IE-WD=Balance

Tran_CODE Tran_Amount
DP 100.00
IE 50.00
WD 20.00

I have tried every possible combination and don’t seem to get a results.

Any Help is appreciated.

Thank You - New Guy
Message was edited by: mwwilliams_640 Message was edited by: mwwilliams_640

Message was edited by: mwwilliams_640

Message was edited by: mwwilliams_640

2

In order to make this work, I have to assume that you have a column in BUF_TRANSACTIONS that can be grouped on, (i.e. CUSTOMER_ID). You cannot create a calculated column that uses data from multiple rows without using grouping.

I created a simple BUFTRANSACTIONS table as follows:

‘ID’ ‘TRAN_CODE’ ‘TRAN_AMOUNT’
1 ‘DP’ 100
1 ‘IE’ 50
1 ‘WD’ 20
2 ‘DP’ 22
2 ‘IE’ 33
2 ‘WD’ 44
3 ‘DP’ 99
3 ‘IE’ 88
3 ‘WD’ 77

Then you can create a calculated column that uses sub-queries to sum the values of each TRAN_CODE and perform the equation you want.

SELECT BUF_TRANS.ID,
(SELECT SUM(A.TRAN_AMOUNT) FROM BUF_TRANSACTIONS A WHERE A.ID = BUF_TRANS.ID AND A.TRAN_CODE = ‘DP’) +
(SELECT SUM(A.TRAN_AMOUNT) FROM BUF_TRANSACTIONS A WHERE A.ID = BUF_TRANS.ID AND A.TRAN_CODE = ‘IE’) -
(SELECT SUM(A.TRAN_AMOUNT) FROM BUF_TRANSACTIONS A WHERE A.ID = BUF_TRANS.ID AND A.TRAN_CODE = ‘WD’) AS CALC
FROM BUF_TRANSACTIONS BUF_TRANS
GROUP BY BUF_TRANS.ID
ORDER BY BUF_TRANS.ID ASC

If you need a finer granularity in your calculation, you can add a group by on a transaction date or whatever other column makes sense.