June 18, 2015 at 2:45 am
I am trying to get a ranking a cross certain columns , basically a weighting depending on criteria.
Using the below code as an example this how far i have got.
Currently it returns all rows with their ranking. However i only need the rows correctly ranked up until the qty that is needed.
So for apples it should only return first line as qty requested is 3.
For oranges it should return top 2 lines as the qty has of 4 has been reached.
DECLARE @Tmp TABLE(fruit VARCHAR(50),shop VARCHAR(10),preference INT, qty INT)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 1',1 ,0)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 2',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 1',1 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 2',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 3',3 ,1)
--APPLE EXAMPLE should return shop 2 first as there are apples here although it not preferred shop but has the qty needed
DECLARE @find3apples INT= 3
SELECT *
,ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= @find3apples THEN 1 ELSE 0 END DESC, t.preference ASC ) AS ranking
FROM @Tmp t WHERE fruit = 'apple'
--ORANGE EXAMPLE Needs to return all 2 shops in order of preference
DECLARE @find4oranges INT= 4
SELECT *
,ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= @find4oranges THEN 1 ELSE 0 END DESC, t.preference ASC) AS ranking
FROM @Tmp t WHERE fruit = 'orange'
So for apples currently get the below in correct ranking but includes the 2nd row which is not needed as i only want qty 3 apples
+-------+--------+------------+-----+---------+
| fruit | shop | preference | qty | ranking |
+-------+--------+------------+-----+---------+
| apple | shop 2 | 2 | 3 | 1 |
| apple | shop 1 | 1 | 0 | 2 |
+-------+--------+------------+-----+---------+
but expect/want this:
+-------+--------+------------+-----+---------+
| fruit | shop | preference | qty | ranking |
+-------+--------+------------+-----+---------+
| apple | shop 2 | 2 | 3 | 1 |
+-------+--------+------------+-----+---------+
Need 4 Oranges, i'm getting this in correct ranking but only need up to 4 qty so 3 + 1 only need top 2 rows back
+--------+--------+------------+-----+---------+
| fruit | shop | preference | qty | ranking |
+--------+--------+------------+-----+---------+
| orange | shop 1 | 1 | 1 | 1 |
| orange | shop 2 | 2 | 3 | 2 |
| orange | shop 3 | 3 | 1 | 3 |
+--------+--------+------------+-----+---------+
But only need top 2 rows as qty will equal 4 (1 + 3)
Expect this:
+--------+--------+------------+-----+---------+
| fruit | shop | preference | qty | ranking |
+--------+--------+------------+-----+---------+
| orange | shop 1 | 1 | 1 | 1 |
| orange | shop 2 | 2 | 3 | 2 |
+--------+--------+------------+-----+---------+
June 18, 2015 at 2:50 am
DELETED
June 18, 2015 at 3:55 am
It is unclear to me what is the expected output. Can you please include it in your post?
Here is a description of how to post it: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
-- Gianluca Sartori
June 18, 2015 at 3:57 am
spaghettidba (6/18/2015)
It is unclear to me what is the expected output. Can you please include it in your post?Here is a description of how to post it: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Updated the top, please let me know if its unclear?
June 18, 2015 at 7:27 am
Getting closer with this query using CTE
DECLARE @Tmp TABLE(fruit VARCHAR(50),shop VARCHAR(10),preference INT, qty INT)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 1',1 ,0)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 2',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 1',1 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 2',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 3',3 ,1)
DECLARE @findqty INT= 4
DECLARE @fruit NVARCHAR(10) = 'orange'
;WITH cte
AS
(
SELECT fruit,shop,t.preference,qty
,ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= @findqty THEN 1 ELSE 0 END DESC, t.preference ASC ) AS ranking
FROM @Tmp t WHERE fruit = @fruit
)
SELECT * FROM cte AS cte_outer
CROSS APPLY (SELECT * FROM cte WHERE cte_outer.ranking = cte.ranking + 1) cte_inner
WHERE cte_outer.qty + cte_inner.qty <= @findqty
June 18, 2015 at 8:06 am
bugg (6/18/2015)
I am trying to get a ranking a cross certain columns , basically a weighting depending on criteria.Using the below code as an example this how far i have got.
Currently it returns all rows with their ranking. However i only need the rows correctly ranked up until the qty that is needed.
So for apples it should only return first line as qty requested is 3.
For oranges it should return top 2 lines as the qty has of 4 has been reached.
DECLARE @Tmp TABLE(fruit VARCHAR(50),shop VARCHAR(10),preference INT, qty INT)INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 1',1 ,0)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 2',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 1',1 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 2',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 3',3 ,1)
--APPLE EXAMPLE should return shop 2 first as there are apples here although it not preferred shop but has the qty needed
DECLARE @find3apples INT= 3
SELECT *
,ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= @find3apples THEN 1 ELSE 0 END DESC, t.preference ASC ) AS ranking
FROM @Tmp t WHERE fruit = 'apple'
--ORANGE EXAMPLE Needs to return all 2 shops in order of preference
DECLARE @find4oranges INT= 4
SELECT *
,ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= @find4oranges THEN 1 ELSE 0 END DESC, t.preference ASC) AS ranking
FROM @Tmp t WHERE fruit = 'orange'
So for apples currently get the below in correct ranking but includes the 2nd row which is not needed as i only want qty 3 apples
+-------+--------+------------+-----+---------+
| fruit | shop | preference | qty | ranking |
+-------+--------+------------+-----+---------+
| apple | shop 2 | 2 | 3 | 1 |
| apple | shop 1 | 1 | 0 | 2 |
+-------+--------+------------+-----+---------+
but expect/want this:
+-------+--------+------------+-----+---------+
| fruit | shop | preference | qty | ranking |
+-------+--------+------------+-----+---------+
| apple | shop 2 | 2 | 3 | 1 |
+-------+--------+------------+-----+---------+
Need 4 Oranges, i'm getting this in correct ranking but only need up to 4 qty so 3 + 1 only need top 2 rows back
+--------+--------+------------+-----+---------+
| fruit | shop | preference | qty | ranking |
+--------+--------+------------+-----+---------+
| orange | shop 1 | 1 | 1 | 1 |
| orange | shop 2 | 2 | 3 | 2 |
| orange | shop 3 | 3 | 1 | 3 |
+--------+--------+------------+-----+---------+
But only need top 2 rows as qty will equal 4 (1 + 3)
Expect this:
+--------+--------+------------+-----+---------+
| fruit | shop | preference | qty | ranking |
+--------+--------+------------+-----+---------+
| orange | shop 1 | 1 | 1 | 1 |
| orange | shop 2 | 2 | 3 | 2 |
+--------+--------+------------+-----+---------+
This one is a poster child for the need for SQL 2012's more capable windowed functions. However, as this is posted in the 2008 forum, we have to use just what's available in SQL 2008. Thus the first order of business is to realize that the order of the original set of records matters. Thus the sample data needs an ordering, and it's not 100% clear to me that just ordering by fruit and shop is going to solve a real-world type of problem. My gut says we order by fruit, then by preference, then by shop. With that query as the first CTE, then you can use that ROW_NUMBER() to create a 2nd CTE that is a running total query. Then, having the running total query, the rows returned have the same fruit as the variable, and the rows for that fruit starting from the lowest RN for that fruit and up to the RN associated with the first running total value that equals or exceeds the quantity required.
Such a set of queries would be considerably easier in SQL 2012, and I don't have the time to work it out today, but perhaps this info will help someone else do so.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2015 at 8:42 am
I agree with Steve: we need to know the logic for ranking rows. SQL2012 would make it considerably easier and more efficient.
This should work, assuming you have to group by fruit and rank by preference.
DECLARE @quantity_needed INT= 4;
DECLARE @fruit_searched varchar(10) = 'orange';
WITH rankedData AS (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY preference)
FROM @Tmp
)
,runningSum AS (
SELECT *
,run_sum = (SELECT SUM(qty) FROM rankedData WHERE fruit = t.fruit AND RN <= t.RN)
FROM rankedData t
WHERE fruit = @fruit_searched
)
SELECT *
FROM runningSum
WHERE qty > 0
AND run_sum <= @quantity_needed;
-- Gianluca Sartori
June 18, 2015 at 8:57 am
spaghettidba (6/18/2015)
I agree with Steve: we need to know the logic for ranking rows. SQL2012 would make it considerably easier and more efficient.This should work, assuming you have to group by fruit and rank by preference.
DECLARE @quantity_needed INT= 4;
DECLARE @fruit_searched varchar(10) = 'orange';
WITH rankedData AS (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY preference)
FROM @Tmp
)
,runningSum AS (
SELECT *
,run_sum = (SELECT SUM(qty) FROM rankedData WHERE fruit = t.fruit AND RN <= t.RN)
FROM rankedData t
WHERE fruit = @fruit_searched
)
SELECT *
FROM runningSum
WHERE qty > 0
AND run_sum <= @quantity_needed;
Thanks for this. Unfortunately using 2008 at the moment not even R2.
Almost there the issue with the above is if i only want 2 oranges. I want to get all of them from the same shop (see qty in RANK ORDER BY)
However this wont work
The ranking for ordering as as shown in the code:
First by stock at each shop, then preference of shop. If all at one shop that would rank higher than getting fruit from different shops by preference.
,ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= @findqty THEN 1 ELSE 0 END DESC, t.preference ASC ) AS rn
June 18, 2015 at 12:09 pm
Thanks for everyone's help, spaghettiDBA almost cracked it. I changed the where statement "WHERE qty > 0 AND (runningSum.run_sum - qty) < @findqty"
Here is the full example below. Last thing I need to do is try factor in how much qty was taken at each line.
DECLARE @Tmp TABLE(fruit VARCHAR(50),shop VARCHAR(10),preference INT, qty INT)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 1',1 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 2',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 3',3 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 4',4 ,2)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 5',5 ,2)
DECLARE @findqty INT= 6
DECLARE @fruit NVARCHAR(10) = 'orange'
;WITH rankedData
AS
(
SELECT fruit,shop,t.preference
,ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= @findqty THEN 1 ELSE 0 END DESC, t.preference ASC ) AS ranking
,qty
FROM @Tmp t WHERE fruit = @fruit
),
runningSum as
(
SELECT *,run_sum = (SELECT SUM(qty) FROM rankedData WHERE fruit = t.fruit AND ranking <= t.ranking),
@findqty AS wanted
--(SELECT SUM(qty) FROM rankedData WHERE fruit = t.fruit AND ranking <= t.ranking)- qty AS chk
FROM rankedData t
WHERE fruit = @fruit
)
SELECT *
FROM runningSum
WHERE qty > 0 AND (runningSum.run_sum - qty) < @findqty
June 18, 2015 at 3:25 pm
What do we do if the quantity from any one shop isn't large enough?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2015 at 3:29 pm
Spreads over to the next shop.
This is sort of what happening with the last code I posted . It correctly brings back the required shops in stock order and preference
However I just need to work out how much stock is taken from each shop. 🙁
June 19, 2015 at 1:19 am
The more i try work this out the more I want to do a loop :laugh: so i can keep a balance of what fruit was taken from what shop.
June 19, 2015 at 1:55 am
This is a situation where a pure set-based solution will not offer a complete solution in a single pass.
It is a variation of the famous "packing bins" problem. Joe Celko offers a solution here: https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/
Hugo Kornelis has a "set-based iteration" solution that seems to be the best of both worlds. http://sqlblog.com/blogs/hugo_kornelis/archive/2011/10/18/bin-packing-part-5-set-based-iteration.aspx
I also have my own solution, based on a variation of this problem.
-- Gianluca Sartori
June 19, 2015 at 2:08 am
Thanks , I'm really struggling to get my head around this.
Is there no way I can keep a running decreasing balance of the the fruit wanted with the SQL i have done 🙁
Even doing a second pass maybe be easier to understand and better then using a loop.
June 19, 2015 at 2:51 am
I think that you should explode your stock quantity using a tally table.
This should do:
DECLARE @Tmp TABLE(fruit VARCHAR(50),shop VARCHAR(10),preference INT, qty INT)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 1',1 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 2',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 3',3 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 4',4 ,2)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 5',5 ,2)
DECLARE @findqty INT= 6;
DECLARE @fruit NVARCHAR(10) = 'orange';
WITH
tally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_columns
),
explodedData AS (
SELECT *
FROM @Tmp AS r
INNER JOIN tally AS t
ON r.qty >= t.n
),
rankedData AS (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY CASE WHEN t.qty >= @findqty THEN 1 ELSE 0 END DESC, t.preference)
FROM explodedData AS t
),
runningSum AS (
SELECT *
,run_sum = (SELECT COUNT(*) FROM rankedData WHERE fruit = t.fruit AND RN <= t.RN)
FROM rankedData t
WHERE fruit = @fruit
)
SELECT fruit, shop, preference, qty AS shop_qty, COUNT(*) AS needed_quantity
FROM runningSum
WHERE qty > 0
AND run_sum <= @findqty
GROUP BY fruit, shop, preference, qty
Doing the same thing for a set of fruits and optimizing how stock is assigned to orders is different (and much more difficult) problem.
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply