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

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 ) )
sqlsql-server


Solution 1:[1]

Just remove (@Amount NOT BETWEEN FromAmount AND ToAmount) after the OR:

DECLARE @Amount INT = 1010

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Zakaria

Related Questions