CODEWITHSUNDEEP
sqlsql-server
user10458185
user10458185

Reputation:

SQL how replace many "IF's" with an efficient query

it's required to get the salary but discounted from some statal entities (health and afp) this entities have differentes % of discount for example, and the ppl will have differents Afp and Health id, all mixed

and

how can I get the salary but not recurring to a many 

IF (afp=1)
  IF (health=1)
  IF (health=2)
  IF (health=3)
IF (afp=2)
  IF (health=1)
  IF (health=2)
  IF (health=3)

and so on, is there a way to avoid these many IF'S? Hope you can help me guys, thnks

Upvotes: 0

Views: 52

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

Just another option is choose()

Example

Select Discount = choose(aft,.05,.10,.15) + choose(health,.02,.02,.03)
 From  YourTable

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You can use a case expression:

select (case when afp = 1 and health = 1 then 0.05
             when afp = 1 and health = 2 then 0.10
             when afp = 1 and health = 3 then 0.15
             when afp = 2 and health = 1 then 0.07
             when afp = 2 and health = 2 then 0.12
             when afp = 2 and health = 3 then 0.18
        end) as discount

Or a reference table, which you can build on the fly:

select t.*, v.discount
from t left join
     (values (1, 1, 0.05), (1, 2, 0.10), . . .
     ) v(afp, health, discount)
     on t.afp = v.afp and t.health = v.health

Upvotes: 1

Related Questions