Need Help in finding data within range

  • shruthy

    SSC Enthusiast

    Points: 126

    Thanks for all support here.

    I have a table which has quantity,price ,item,date. I am trying to develop a stored procedure

    where I pass the quantity and item name and fetch the price applicable for the quantity range for the date. The data in this table can be any number of records for an item.

    Example

    item price quantity date

    aaa 5 300 2016/9

    aaa 10 200 2016/9

    aaa 15 100 2016/9

    aaa 6 250 2015/5

    aaa 15 50 2015/5

    bbb 50 100 2016/9

    bbb 30 300 2016/9

    ccc 40 150 2016/8

    ccc 80 50 2016/8

    ccc 100 30 2016/8

    Now if I pass item aaa and quantity 50, it should give me price of 15 as a result. It should ignore all data that

    is for year 2015 as we now have latest data for the item for year 2016.

    The search should work as a range for quantity fetching its corresponding price.

    like for item a qty 0-100 price 15

    qty 100 to 200 price 10

    qty 200 to 300 price 5

    and > 300 price 5

    I am not sure how to approach to the solution. Did lots of research and brain crunching...but no good solution.

    I appreciate your help.

    Thanks,

    shruthy

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    Several ways of doing this, here is one fairly straight forward method

    😎

    Note: added few entries to the sample data.

    USE TEEST;

    GO

    SET NOCOUNT ON;

    --- Query parameters

    DECLARE @ITEM CHAR(3) = 'aaa';

    DECLARE @QUANTITY INT = 15;

    --- Sample dataset CTE

    ;WITH SAMPLE_DATA (item,price,quantity,date) AS

    ( SELECT item,price,quantity,CONVERT(DATE,date,111)

    FROM (VALUES

    ('aaa', 5,300, '2016/09/01')

    ,('aaa', 10,200, '2016/09/01')

    ,('aaa', 15,100, '2016/09/01')

    ,('aaa', 16, 50, '2016/09/01')

    ,('aaa', 17, 30, '2016/09/01')

    ,('aaa', 18, 10, '2016/09/01')

    ,('aaa', 19, 1, '2016/09/01')

    ,('aaa', 6,250, '2015/05/01')

    ,('aaa', 14, 50, '2015/05/01')

    ,('bbb', 50,100, '2016/09/01')

    ,('bbb', 30,300, '2016/09/01')

    ,('ccc', 40,150, '2016/08/01')

    ,('ccc', 80, 50, '2016/08/01')

    ,('ccc',100, 30, '2016/08/01')

    ) X(item,price,quantity,date)

    )

    SELECT

    TOP(1) SD.price

    FROM SAMPLE_DATA SD

    WHERE SD.item = @ITEM

    AND SD.quantity <= @QUANTITY

    ORDER BY SD.quantity DESC

    ,SD.date DESC;

    Output

    price

    ------

    18

  • shruthy

    SSC Enthusiast

    Points: 126

    Thanks Eirikur Eiriksson for your quick reply. It works well.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    You are very welcome.

    😎

    Note that there must be entries in the set that cover the full range, otherwise the query will not return anything when querying for missing ranges.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply