Home NOT BETWEEN gives error when work with amount range. when no range of amount matched

Questions

NOT BETWEEN gives error when work with amount range. when no range of amount matched

March 3, 2022

I just want to get a row that has an amount ranges between 2 data columns, and if no range matched then Commission row with null value fetched.

+----------+------------+-----------+------------+--------+
|PackageID | FromAmount |  ToAmount | Commission | TypeID |
|   1      |    1       |      100  |    2       |   3    |
|   2      |   101      |      500  |     3      |   1    | 
|   3      |   501      |     1000  |     4      |   2    |
|   4      |   NULL     |     NULL  |     6      |   1    |
+----------+------------+-----------+------------+--------+

Query –

DECLARE @Amount INT = 1010

SELECT FromAmount, ToAmount, Commission, TypeID
FROM Package 
WHERE ((@Amount BETWEEN FromAmount AND ToAmount) OR (@Amount NOT BETWEEN FromAmount AND ToAmount) AND (FromAmount IS NULL AND ToAmount IS NULL))

>Solution :

I would write this as:

SELECT FromAmount, ToAmount, Commission, TypeID
FROM Package
WHERE @Amount BETWEEN FromAmount AND ToAmount OR
      NOT EXISTS (SELECT 1 FROM Package
                  WHERE @Amount BETWEEN FromAmount AND ToAmount) AND
      FromAmount IS NULL AND ToAmount IS NULL;