[dba-SQLServer] Calculated Column Field in a table...

Hi there,

I would have thought this was pretty simple, but I have a great ability to complicate these things it seems - heh, early days for me SQL server.
I have three fields in the table that I want to use for a calculated result in a couple of other fields in the table.

"AccTreatmentID"
1: Opex
2: Capex

"AccountingSplit"
0%-100%

"Dollars"
Manually entered dollar value


Then I have two other fields (Capex and Opex) and I (think I) want to do this.

"=iif([AccTreamtentID]=1,([Dollars]*[AccountingSplit]),0)" to get the Opex Value
"=iif([AccTreamtentID]=2,([Dollars]*[AccountingSplit]),0)" to get the Capex Value

or If AccTreatment is Capex, then get the Capex Percentage and multiply by the dollar value to get the capex amount.
The actual formula is more complex as I need to determine the balance of the percentage and then apply that to the remainder of the value (opex in this case). anyway I am trying to keep it simple to start with.  (plus I will probably need some sort of error test to return zero if part of the data is missing - Am not sure if I need this or not in SQL server.)

The syntax in these formulae are not valid so I would like some help with that.  And I guess my other question is do I actually need this at all?

Maybe I should do all the hack work in a query to report of Opex / Capex splits.  Part of me suspect I dont need to store the calculated data at all in the table at all and this is bad practise as it takes up time and space for no real reason.

Anyone got any thoughts on this.

Cheers
Darryll

This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses.  No warranty is made that this material is free from computer virus or any other defect or error.  Any loss/damage incurred by using this material is not the sender's responsibility.  The sender's entire liability will be limited to resupplying the material.