CODEWITHSUNDEEP
sqlsql-server
Pushpendra Poonia
Pushpendra Poonia

Reputation: 11

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

Upvotes: 0

Views: 68

Answers (2)

Zakaria
Zakaria

Reputation: 4806

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

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;

Upvotes: 0

Related Questions