Reputation: 10815
i am having a table with fields
item_no item_amount_from item_amount_to price
101 200 300 10
101 300 400 20
101 400 500 30
101 500 null 40
i want to make a query which can retrieve price if item amounts are in between (item_amount_from) to (item_amount_to) range please let me know how should i wit the query
Upvotes: 0
Views: 278
Reputation: 166486
Have a look at something like this.
I will note though that you are missing the opening row. Something like 101, null, 200, 5
DECLARE @Table TABLE(
item_no INT,
item_amount_from FLOAT,
item_amount_to FLOAT,
price FLOAT
)
INSERT INTO @Table SELECT 101,200,300,10
INSERT INTO @Table SELECT 101,300,400,20
INSERT INTO @Table SELECT 101,400,500,30
INSERT INTO @Table SELECT 101,500,null,40
DECLARE @Amount FLOAT,
@ItemNO INT
SELECT @Amount = 510,
@ItemNO = 101
SELECT *
FROM @Table
WHERE item_no = @ItemNO
AND (
((item_amount_from <= @Amount AND item_amount_to > @Amount))
OR (@Amount >= item_amount_from AND item_amount_to IS NULL)
OR (@Amount <= item_amount_to AND item_amount_from IS NULL)
)
Upvotes: 1