June 19, 2015 at 4:03 am
spaghettidba (6/19/2015)
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.
This is awesome thanks spaghettidba, the only issue is if a shop has 1000's of fruits this will be slow as all stock is separated into individual lines. I'm trying to only split stock out upto the amount of fruit needed
June 19, 2015 at 4:30 am
If you have thousands of fruits, probably sys.all_columns does not have enough rows for your tally table. You may need to CROSS JOIN with sys.all_columns again and select just the TOP rows you want to match the maximum amount owned by a store:
SELECT TOP(100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_columns AS a
CROSS JOIN sys.all_columns AS b
However, I tried with thousands of fruits and it is not slow at all.
Obviously, your actual code is probably running on different tables...
-- Gianluca Sartori
June 19, 2015 at 4:32 am
Sorry, I repeated my tests and yes, it's dog slow 🙂
-- Gianluca Sartori
June 19, 2015 at 4:36 am
I just noticed that I'm an idiot: the RN column is already the running sum in this case, so you don't need to calculate it with another triangular join:
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 ,1000)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 2',2 ,3000)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 3',3 ,1000)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 4',4 ,2000)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 5',5 ,2000)
DECLARE @findqty INT= 6000;
DECLARE @fruit NVARCHAR(10) = 'orange';
WITH
tally AS (
SELECT TOP(100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_columns AS a
CROSS JOIN sys.all_columns AS b
),
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
WHERE fruit = @fruit
)
SELECT fruit, shop, preference, qty AS shop_qty, COUNT(*) AS needed_quantity
FROM rankedData
WHERE qty > 0
AND RN <= @findqty
GROUP BY fruit, shop, preference, qty
This way, it's lightning fast even with high stocks.
-- Gianluca Sartori
June 19, 2015 at 4:40 am
spaghettidba (6/19/2015)
Sorry, I repeated my tests and yes, it's dog slow 🙂
Yep it slows right down when there are 1000's at each shop. :laugh:
Do you think if I split the fruit wanted onto single lines it would make things easier?
so 5 oranges is 5 single lines? Then assign a shop to each line depending on ranking / stock level?
This killing me 🙁 , do you think a loop would be dire for this sort of operation.
In saying we get a 1000s of transactions daily so I should probably avoid the loop.
June 19, 2015 at 4:48 am
I assume you didn't see my latest attempt. It should solve your speed issues.
-- Gianluca Sartori
June 19, 2015 at 5:01 am
spaghettidba (6/19/2015)
I assume you didn't see my latest attempt. It should solve your speed issues.
Yep spotted it , just testing it now .. You are a legend!!! thank you for all you help on this.:-D
This is using it against a table of purchases. I have tally table already in the DB that has 1000 entries. Don't think well have purchase greater than 50 per fruit
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 ,4)
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 ,5)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 4',4 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 5',5 ,4)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 5',2 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 5',5 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('strawberry','shop 1',1 ,5000)
DECLARE @purchase TABLE(fruit nvarchar(50), findqty INT)
INSERT INTO @purchase (fruit,findqty)
VALUES ( 'orange', 10 )
,( 'apple', 4 )
,( 'strawberry', 4 )
;WITH
explodedData AS (
SELECT r.*
FROM @Tmp AS r
INNER JOIN @purchase p ON r.fruit = p.fruit
INNER JOIN tally AS t ON r.qty >= t.n
),
rankedData AS (
SELECT t.*,p.findqty, RN = ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= p.findqty THEN 1 ELSE 0 END DESC, t.preference)
FROM explodedData AS t INNER JOIN @purchase p ON p.fruit = t.fruit
)
SELECT fruit, shop, preference, qty AS shop_qty, COUNT(*) AS needed_quantity
FROM rankedData
WHERE qty > 0 AND RN <= findqty
GROUP BY fruit, shop, preference, qty
June 19, 2015 at 6:03 am
Ah, great. Glad I could help.
Might sound like a weird question, but: is this really a database for selling fruit? 🙂
-- Gianluca Sartori
June 19, 2015 at 6:09 am
spaghettidba (6/19/2015)
Ah, great. Glad I could help.Might sound like a weird question, but: is this really a database for selling fruit? 🙂
🙂 not fruit but selling other products, I just simplified it (well tried to) 😉
June 19, 2015 at 6:23 am
bugg (6/19/2015)
spaghettidba (6/19/2015)
Ah, great. Glad I could help.Might sound like a weird question, but: is this really a database for selling fruit? 🙂
🙂 not fruit but selling other products, I just simplified it (well tried to) 😉
You did a great job. 😉
-- Gianluca Sartori
June 24, 2015 at 2:12 am
There is always a final hurdle! :crying:
I've hit a issue where if there isn't enough stock at shop i get less then the actual desired quantity. I need to assign the correct needed quantity even if there isn't enough stock to cover it at any shop.
So in this example I've requested 4 apples but only 2 are put on the order as we only have 2 in stock.
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 ,4)
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 ,5)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 4',4 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 5',5 ,4)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 5',1 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 5',2 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('strawberry','shop 1',1 ,5000)
DECLARE @purchase TABLE(fruit nvarchar(50), findqty INT)
INSERT INTO @purchase (fruit,findqty)
VALUES ( 'orange', 10 )
,( 'apple', 4 )
,( 'strawberry', 4 )
;WITH
explodedData AS (
SELECT r.*
FROM @Tmp AS r
INNER JOIN @purchase p ON r.fruit = p.fruit
INNER JOIN tally AS t ON r.qty >= t.n
),
rankedData AS (
SELECT t.*,p.findqty, RN = ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= p.findqty THEN 1 ELSE 0 END DESC, t.preference)
FROM explodedData AS t INNER JOIN @purchase p ON p.fruit = t.fruit
)
SELECT fruit, shop, preference, qty AS shop_qty, COUNT(*) AS needed_quantity
FROM rankedData
WHERE qty > 0 AND RN <= findqty
GROUP BY fruit, shop, preference, qty
I was thinking of shoving all the above in a temp table then summing the needed quantity and comparing it against the actual needed quantity and any adding the difference to the last shop?
June 24, 2015 at 2:23 am
I don't understand what should happen if there is not enough stock to cover the order. Should the fruit be ignored?
Can you post the expected output based on that sample data?
-- Gianluca Sartori
June 24, 2015 at 2:37 am
If you have to eliminate fruit that are not completely covered by the stock, this should work:
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
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 ,4)
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 ,5)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 4',4 ,3)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('orange','shop 5',5 ,4)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 5',1 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('apple','shop 5',2 ,1)
INSERT INTO @Tmp(fruit,shop,preference,qty) VALUES ('strawberry','shop 1',1 ,5000)
DECLARE @purchase TABLE(fruit nvarchar(50), findqty INT)
INSERT INTO @purchase (fruit,findqty)
VALUES ( 'orange', 10 )
,( 'apple', 4 )
,( 'strawberry', 4 )
;WITH
explodedData AS (
SELECT r.*
FROM @Tmp AS r
INNER JOIN @purchase p ON r.fruit = p.fruit
INNER JOIN tally AS t ON r.qty >= t.n
WHERE EXISTS (
SELECT *
FROM @Tmp AS r2
GROUP BY fruit
HAVING SUM(qty) >= (SELECT SUM(findqty) FROM @purchase WHERE fruit = r2.fruit)
AND fruit = r.fruit
)
),
rankedData AS (
SELECT t.*,p.findqty, RN = ROW_NUMBER() OVER (PARTITION BY t.fruit ORDER BY CASE WHEN t.qty >= p.findqty THEN 1 ELSE 0 END DESC, t.preference)
FROM explodedData AS t INNER JOIN @purchase p ON p.fruit = t.fruit
)
SELECT fruit, shop, preference, qty AS shop_qty, COUNT(*) AS needed_quantity
FROM rankedData AS RD
WHERE qty > 0 AND RN <= findqty
GROUP BY fruit, shop, preference, qty
Basically, you try to eliminate the fruit not completely covered as soon as you can (before exploding for quantity at least).
-- Gianluca Sartori
June 24, 2015 at 2:47 am
Thanks , I think what I need to try do is assign the cant find fruit to a new "shop" which will be pending stock.
So keep everything how it is with the awesome code :-D, but for the 2 remaining unassigned apples assign them to a new holding shop. :crazy:
June 24, 2015 at 3:20 am
I tried to play with the code a bit, but frankly I have no time. Probably your initial idea of using a temp table and compute the sums is the way to go.
-- Gianluca Sartori
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply