CODEWITHSUNDEEP
sql
Vladislav
Vladislav

Reputation: 7

Calculated Field withMultiple If

I am struggling to create a calculated field within my SQL query.

The query reports some statistics and Column E has a figure taken from the database, let's call it "Contract Award Value".

Based on that figure I need to calculate a fee that we will be charging. We used excel previously with the following function:

=IF(E1<100000,2500+E1*0.005,IF(E1<1000000,5000+E1*0.005,IF(E1>=1000000,7500+E1*0.005)))

Is there any chance to add a line in SQL that will calculate how much we need to charge based on the formula above? For clarity, if E1 is less than £100k, the charge is £2,500 + 0.5%; If between £100k and £1m, the charge is £5,000 + 0.5%; If over £1,000,000, the charge is £10,000 + 0.5%.

I tried to look for the answer in similar topics but did not find anything that could help.

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

In SQL, you would express this using a case expression:

(case when E1 < 100000 then 2500 + E1 * 0.005
      when E1 < 1000000 then 5000 + E1 * 0.005
      when E1 >= 1000000 then 7500 + E1 * 0.005
 end) as fee

This assumes that E1 is a column in your data.

Upvotes: 2

Diptee Hamdapurkar
Diptee Hamdapurkar

Reputation: 391

You can create a function which will give you expected result and you can call that function in your query

Upvotes: 0

Related Questions