CODEWITHSUNDEEP
sql-servert-sql
BouvierX
BouvierX

Reputation: 33

How do I nest if then statements with select statements including other if then statements?

I'm fairly new to SQL and can't figure out how to combine several if .. then statements.

What is the right syntax for this?

I'm using SQL Server Management Studio 2017. I've tried to combine if... else if..statements and I tried using case statements, but I always get lost in the nesting of the statements.

I have several condtions whom have to be met before I can execute some sort of calculation.

It should be something like this:

If CalculationMethod = x 

and if (Price * coefficient) < Amount

then CalculatedAmount = Amount

else CalculatedAmount = (Price * coefficient)

Where Amount has it's own if statements:

Amount =

If Category = a and DistanceFrom <= Distance >= DistanceUntill then take amount from that particular cell

If Category = b and DistanceFrom <= Distance >= DistanceUntill then take amount from that particular cell

If Category = c and DistanceFrom <= Distance >= DistanceUntill then take amount from that particular cell

In this case, Amount is a cell in a table with columns DistanceFrom, DistanceUntill, a, b and c.

CalculationMethod and Coefficient are columns in another table. Price is a column in third table.

In the end I want the CalculatedAmount based on the Amount, Price and Coefficient.

Does this make any sense? Does anyone has an idea on how to tackle this?

Upvotes: 0

Views: 59

Answers (2)

JMabee
JMabee

Reputation: 2300

If you have an IF...THEN...ELSE type of scenario I think the right direction would be to use a CASE statement such as:

SELECT CASE WHEN CalculationMethod = x AND ((Price * coefficient) < Amount) THEN Amount
        ELSE (Price * coefficient) END CalculatedAmount

You can read about it here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

An IIF clause works very well when there is only one decision branch, but if you have multiple things to choose from the CASE statement is the way to go.

Upvotes: 1

Cato
Cato

Reputation: 3701

 SELECT IIF(CalculationMethod = x and Price * coefficient < Amount, Amount, Price * coefficient) as CalculatedAmount 
      FROM aTable

Upvotes: 0

Related Questions