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

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

CodePudding user response：

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

CodePudding user response：

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;