September 30, 2016 at 4:57 pm
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
October 2, 2016 at 1:58 am
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
October 2, 2016 at 2:31 pm
Thanks Eirikur Eiriksson for your quick reply. It works well.
October 2, 2016 at 10:09 pm
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.
April 5, 2023 at 6:16 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply