How to use SQL Case Expression using BETWEEN and OR?

Question

I am trying to use a Case Expression to find total expenditures between -.49 and -.01 OR between .50 and .01.

Here is what I've tried:

CASE WHEN IsNull(FYExpend.TotalExpenditures,0) Between -.49 AND -.01
          OR IsNull(FYExpend.TotalExpenditures,0) Between .50 AND .01
     THEN 'True' ELSE 'False' END AS 'IsImmaterial'

The second part is not evaluating correctly as I have many records between .50 and .01 that are returning as false. Based on everything I have read this syntax should work. I haven't found any questions here specific to using BETWEEN and OR in the same statement. Please let me know if there are related questions.

Does anyone know why this isn't working?

Answer 1

The second condition has the interval values the wrong way around:

CASE 
    WHEN FYExpend.TotalExpenditures,0 Between -0.49 AND -0.01
    OR IsNull(FYExpend.TotalExpenditures,0) Between 0.01 AND 0.50
 THEN 'True' ELSE 'False' END AS IsImmaterial

Notes:

• you don't need ISNULL, since 0 does not belong to the any of the sought intervals anyway

• the column alias should not be surrounded with single quotes