How to calculate price in column based on additional criterias?

Question

How do I calculate final price in table items based on criterias? Criterias would be set in table modifier. I was thinking about left join but is it possible in stored procedure to choose type of modifier? Every records would have only one criteria, they cant be combined.

items:

code   |   price_general
---------------------------------
BIKE   |   50
CAR    |   300
BOAT   |   600
PLANE  |   1200

modifier:

type   |   item        |  amount
----------------------------------
PERC   |   CAR         |  20   (add 20% on top)
FIXE   |   BOAT        |  700  (fixed price 700)
ADD    |   PLANE       |  10   (add +10 value)

result should look like this

code   |   price_general    |    price_final
-------------------------------------------------
BIKE   |   50               |    50
CAR    |   300              |    360
BOAT   |   600              |    700
PLANE  |   1200             |    1210

Is this possible in TSQL? Or should I add additional business logic to C# code?

Thank you for your ideas.

Answer 1

You could use a CASE statement with your logic. Something like:

SELECT i.code, i.price_general,
price_final = 
CASE m.type  
WHEN 'PERC' THEN i.price_general*(1 + m.amount/100)
WHEN 'FIXE' THEN m.amount
WHEN 'ADD' THEN i.price_general + m.amount
ELSE i.price_general
END
FROM items i LEFT JOIN modifier m on i.item = m.code;

Apologies for any typos or syntax errors, I use Postgresql, but I hope you get the idea...