I want to have a SQL computed column according to rules

Question

Hi all!

I want to be able to store a decimal value in one column, and in the other column to store an int that represents the option (will explain):

1. should be base -%
2. should be base -absolute
3. should be base +%
4. should be base +absolute
5. 1 & 2 is a discount
6. 3 & 4 is an upcharge
7. 1 & 3 reduces/raises the amount by percentage (i.e. amount * amount/value).
8. 2 & 4 reduces/raises the amount absolutely (i.e. amount +/- value).

Meaning I have a table of 3 columns:

• BasePrice money
• AdditionalPrice decimal
• Option tinyint

and

• ComputedColumn (decimal?)

let's say we have a row that it's BasePrice is 100 and the AdditionalPrice is 0.20

According to the option the computed col should generate the following value:

• 80
• 99.80
• 120
• 100.20

Am I clear? Is there any way I can achieve this?

Answer 1

I'd keep the computed column as money too.

Internally, this will cast to decimal because of Data Type Precedence

CREATE TABLE (
...,
ComputedColumn AS CAST (
    CASE Option
        WHEN 1 THEN BasePrice * (1 - AdditionalPrice)
        WHEN 2 THEN BasePrice - AdditionalPrice
        WHEN 3 THEN BasePrice * (1 + AdditionalPrice)
        WHEN 4 THEN BasePrice + AdditionalPrice
    END AS money)
)