SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help in finding data within range


Need Help in finding data within range

Author
Message
shruthy
shruthy
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 77
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
Eirikur Eiriksson
SSC Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159392 Visits: 23329
Several ways of doing this, here is one fairly straight forward method
Cool

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
shruthy
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 77
Thanks Eirikur Eiriksson for your quick reply. It works well.
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159392 Visits: 23329
You are very welcome.
Cool

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search