Hello,

In your Stairway to T-SQL Beyond The Basics Level 6 Using the CASE Expression and IIF Function covering the Listing 7: Nesting CASE statement lesson. If I was to add to your “MyOrder” table the values;

('01-15-2015', 301.00,NULL), and then run your code, the result for my added value would return. Meaning that if the OrderAmt_Category is Null it would be a layaway. I would think that it should be without layaway.

301.00 NULL300 Dollar Order with Layaway

USE tempdb

GO

SELECT OrderAmt, Layaway,

CASE

WHEN OrderAmt < 100 THEN '< 100 Dollar Order'

WHEN OrderAmt < 200 THEN '100 Dollar Order'

WHEN OrderAmt < 300 THEN

CASE

WHEN Layaway = 'N'

THEN '200 Dollar Order without Layaway'

ELSE '200 Dollar Order with Layaway' END

ELSE

CASE

WHEN Layaway = 'N'

THEN '300 Dollar Order without Layaway'

ELSE '300 Dollar Order with Layaway' END

END AS OrderAmt_Category

FROM MyOrder

order by OrderAmt;

-----------Reorder/Change the 2nd and 3rd CASE statements to accomodate the "Null" values correctly

USE tempdb

GO

SELECT OrderAmt, Layaway,

CASE

WHEN OrderAmt < 100 THEN '< 100 Dollar Order'

WHEN OrderAmt < 200 THEN '100 Dollar Order'

WHEN OrderAmt < 300 THEN

CASE

WHEN Layaway = 'Y'

THEN '200 Dollar Order with Layaway'

ELSE '200 Dollar Order without Layaway' END

ELSE

CASE

WHEN Layaway = 'Y'

THEN '300 Dollar Order with Layaway'

ELSE '300 Dollar Order without Layaway' END

END AS OrderAmt_Category

FROM MyOrder

order by OrderAmt;

Great article and very helpful