AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

Nested iif() functions vs the switch() function

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Working with multiple conditions and criteria

Determine discount amounts based on customer sales volumes

Discussion:

Our sales manager wants to provide order discounts to customers based on their historical sales volume with the company.  Thus, if a customer has a certain recorded sales volume, the next time she orders, she will get a predetermined discount rate regardless of her new order amount. 

If the total orders amount for a customer is less than $200, that customer will receive no discounts.  If it is between $200 and $300, the customer will get a 5% discount.  If it is between $300 and $500, the customer will get a 10% discount.  For anything above that, our manager will offer a generous 25% discount to the customer.  Our job is to create a datasheet that will list customers and show the corresponding discount rate for each customer. 

Solution 1: using nested iif() functions

Our first option is to use a series of nested iif() functions as shown below—four iif() functions, one inside the other to accomplish our goal.  However, the use of nested iif() functions is a convoluted process.  In addition, if we need to change the business logic behind our statement by adding additional categories, the task is not a clear and clean preposition.  The problem originates from the fact that the iif() function takes only two arguments.  For more outcomes or categories, we need to nest, which, in turn, results in complicated statements. 

Code:

SELECT lastname, firstname, OrderTotal, IIf([ordertotal]<200,0,IIf([ordertotal]>=200 And [Ordertotal]<300,'5%',IIf([ordertotal]>=300 And [ordertotal]<500,'10%',IIf([ordertotal]>500,'25%')))) AS Discount

FROM Qry_Conditions;

 

Result:

 

Solution 2: using the switch() function

In this example, the first observation is that the switch() function does not limit the number of expressions we can use, so we need no nesting.  In addition, our logic becomes immediately apparent to both us and anyone else who will need to edit the SQL statement later on.  The cleanliness of the SQL statement is obvious: 

Code:

SELECT lastname, firstname, ordertotal,

SWITCH(

ordertotal < 200,                                             "0" ,

ordertotal>= 200 and ordertotal < 300,        "5%" ,

ordertotal> 300 and ordertotal <= 500,      "10%" ,

ordertotal> 500 ,                                            "25%"

)

AS Discount 

FROM qry_conditions

 

Result: