NoviceToDotNet
NoviceToDotNet

Reputation: 10815

sql query to find a price, for item amount between the range

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

Answers (1)

Adriaan Stander
Adriaan Stander

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

Related Questions