June 24, 2015 at 3:44 am
Maybe this?
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
),
assignedStock AS (
SELECT fruit, shop, preference, qty AS shop_qty, COUNT(*) AS needed_quantity, MIN(findqty) AS total_needed
FROM rankedData
WHERE qty > 0 AND RN <= findqty
GROUP BY fruit, shop, preference, qty
),
intermediateData AS (
SELECT *, SUM(needed_quantity) OVER(PARTITION BY fruit) AS total_found
FROM assignedStock
)
SELECT *
FROM intermediateData
UNION ALL
SELECT DISTINCT
fruit,
'unassigned' AS shop,
0 AS preference,
0 AS shop_qty,
total_needed - total_found AS needed_quantity,
total_needed,
0 AS total_found
FROM intermediateData
WHERE total_needed > total_found
-- Gianluca Sartori
June 24, 2015 at 4:26 am
spaghettidba (6/24/2015)
Maybe this?
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
),
assignedStock AS (
SELECT fruit, shop, preference, qty AS shop_qty, COUNT(*) AS needed_quantity, MIN(findqty) AS total_needed
FROM rankedData
WHERE qty > 0 AND RN <= findqty
GROUP BY fruit, shop, preference, qty
),
intermediateData AS (
SELECT *, SUM(needed_quantity) OVER(PARTITION BY fruit) AS total_found
FROM assignedStock
)
SELECT *
FROM intermediateData
UNION ALL
SELECT DISTINCT
fruit,
'unassigned' AS shop,
0 AS preference,
0 AS shop_qty,
total_needed - total_found AS needed_quantity,
total_needed,
0 AS total_found
FROM intermediateData
WHERE total_needed > total_found
Wow! you rock, this looks like it work very well indeed. Thankyou! I wasn't aware that you could reference a result set earlier up in the CTE chain using the UNION like that.
June 24, 2015 at 5:35 am
I wonder how a rCTE version would fare, in terms of performance?
DECLARE @Tmp TABLE (fruit VARCHAR(50), shop VARCHAR(10), preference INT, InStock INT)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 1',1 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 2',2 ,3)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 3',3 ,5)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 4',4 ,3)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 5',5 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('apple','shop 5',1 ,1)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('apple','shop 5',2 ,1)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('strawberry','shop 1',1 ,5000);
DECLARE @purchase TABLE (fruit varchar(50), OrderQuantity INT)
INSERT INTO @purchase (fruit, OrderQuantity)
VALUES ( 'orange', 10 )
,( 'apple', 4 )
,( 'strawberry', 5001 );
--------------------------------------------------------------------------------------------
IF OBJECT_ID('Tempdb..#StreamedData') IS NOT NULL DROP TABLE #StreamedData;
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
s.*, p.OrderQuantity
INTO #StreamedData
FROM @Tmp s
INNER JOIN @purchase p ON p.fruit = s.fruit;
WITH Calculator AS (
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity,
ReservedFromShop = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.InStock ELSE tr.OrderQuantity END,
Outstanding = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.OrderQuantity - tr.InStock ELSE 0 END
FROM #StreamedData tr
WHERE rn = 1
UNION ALL
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity,
ReservedFromShop = CASE WHEN lr.Outstanding > tr.InStock THEN tr.InStock ELSE lr.Outstanding END,
Outstanding = CASE WHEN lr.Outstanding > tr.InStock THEN lr.Outstanding - tr.InStock ELSE 0 END
FROM #StreamedData tr -- this row
INNER JOIN Calculator lr -- last row
ON lr.fruit = tr.fruit
AND lr.rn+1 = tr.rn
)
SELECT *
FROM Calculator
ORDER BY fruit, rn;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 24, 2015 at 6:50 am
ChrisM@Work (6/24/2015)
I wonder how a rCTE version would fare, in terms of performance?
Brilliant! Works like a treat!
-- Gianluca Sartori
June 24, 2015 at 6:59 am
spaghettidba (6/24/2015)
ChrisM@Work (6/24/2015)
I wonder how a rCTE version would fare, in terms of performance?Brilliant! Works like a treat!
I've recently implemented a solution here which is essentially the same as the one posted and out of the box it's "quick enough". With a clustered index on the temp table partitions (in this thread, "fruit") + rn it's proper speedy.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2015 at 3:00 am
ChrisM@Work (6/24/2015)
I wonder how a rCTE version would fare, in terms of performance?
Very cool 🙂
I've amended the code to pull in unassigned order quantities similar to spaghetti's output 🙂
DECLARE @Tmp TABLE (fruit VARCHAR(50), shop VARCHAR(10), preference INT, InStock INT)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 1',1 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 2',2 ,3)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 3',3 ,5)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 4',4 ,3)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 5',5 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('apple','shop 5',1 ,1)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('apple','shop 5',2 ,1)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('strawberry','shop 1',1 ,5000);
DECLARE @purchase TABLE (fruit varchar(50), OrderQuantity INT)
INSERT INTO @purchase (fruit, OrderQuantity)
VALUES ( 'orange', 10 )
,( 'apple', 4 )
,( 'strawberry', 5001 );
--------------------------------------------------------------------------------------------
IF OBJECT_ID('Tempdb..#StreamedData') IS NOT NULL DROP TABLE #StreamedData;
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
s.*, p.OrderQuantity
INTO #StreamedData
FROM @Tmp s INNER JOIN @purchase p ON p.fruit = s.fruit;
WITH Calculator AS (
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity,
ReservedFromShop = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.InStock ELSE tr.OrderQuantity END,
Outstanding = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.OrderQuantity - tr.InStock ELSE 0 END
FROM #StreamedData tr
WHERE rn = 1
UNION ALL
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity,
ReservedFromShop = CASE WHEN lr.Outstanding > tr.InStock THEN tr.InStock ELSE lr.Outstanding END,
Outstanding = CASE WHEN lr.Outstanding > tr.InStock THEN lr.Outstanding - tr.InStock ELSE 0 END
FROM #StreamedData tr -- this row
INNER JOIN Calculator lr -- last row
ON lr.fruit = tr.fruit
AND lr.rn + 1 = tr.rn
)
SELECT * FROM Calculator
UNION ALL
SELECT rn,fruit,'unassigned' AS shop,c.preference,c.InStock,c.OrderQuantity,c.ReservedFromShop,c.Outstanding FROM Calculator c
WHERE rn in (SELECT MAX(rn) FROM Calculator c1 WHERE c.fruit = c1.fruit)
AND c.Outstanding > 0
ORDER BY fruit, rn;
June 25, 2015 at 3:11 am
bugg (6/25/2015)
ChrisM@Work (6/24/2015)
I wonder how a rCTE version would fare, in terms of performance?Very cool 🙂
I've amended the code to pull in unassigned order quantities similar to spaghetti's output 🙂
DECLARE @Tmp TABLE (fruit VARCHAR(50), shop VARCHAR(10), preference INT, InStock INT)INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 1',1 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 2',2 ,3)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 3',3 ,5)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 4',4 ,3)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop 5',5 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('apple','shop 5',1 ,1)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('apple','shop 5',2 ,1)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('strawberry','shop 1',1 ,5000);
DECLARE @purchase TABLE (fruit varchar(50), OrderQuantity INT)
INSERT INTO @purchase (fruit, OrderQuantity)
VALUES ( 'orange', 10 )
,( 'apple', 4 )
,( 'strawberry', 5001 );
--------------------------------------------------------------------------------------------
IF OBJECT_ID('Tempdb..#StreamedData') IS NOT NULL DROP TABLE #StreamedData;
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
s.*, p.OrderQuantity
INTO #StreamedData
FROM @Tmp s INNER JOIN @purchase p ON p.fruit = s.fruit;
WITH Calculator AS (
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity,
ReservedFromShop = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.InStock ELSE tr.OrderQuantity END,
Outstanding = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.OrderQuantity - tr.InStock ELSE 0 END
FROM #StreamedData tr
WHERE rn = 1
UNION ALL
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity,
ReservedFromShop = CASE WHEN lr.Outstanding > tr.InStock THEN tr.InStock ELSE lr.Outstanding END,
Outstanding = CASE WHEN lr.Outstanding > tr.InStock THEN lr.Outstanding - tr.InStock ELSE 0 END
FROM #StreamedData tr -- this row
INNER JOIN Calculator lr -- last row
ON lr.fruit = tr.fruit
AND lr.rn + 1 = tr.rn
)
SELECT * FROM Calculator
UNION ALL
SELECT rn,fruit,'unassigned' AS shop,c.preference,c.InStock,c.OrderQuantity,c.ReservedFromShop,c.Outstanding FROM Calculator c
WHERE rn in (SELECT MAX(rn) FROM Calculator c1 WHERE c.fruit = c1.fruit)
AND c.Outstanding > 0
ORDER BY fruit, rn;
Try creating MAX(rn) in the source table #StreamedData: COUNT(*) OVER(PARTITION BY ...) - it's "cleaner" and probably much cheaper 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2015 at 3:54 am
Try creating MAX(rn) in the source table #StreamedData: COUNT(*) OVER(PARTITION BY ...) - it's "cleaner" and probably much cheaper 😎
Something like this:
SELECT *,max(rn) over(partition by fruit) AS max_rn INTO #StreamedData
FROM
(
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
s.*,p.OrderQuantity
FROM @Tmp s
INNER JOIN @purchase p ON p.fruit = s.fruit
)rd
June 25, 2015 at 4:01 am
Yes, or this which looks a little cleaner:
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
max_rn = COUNT(*) OVER(PARTITION BY s.fruit),
s.*,
p.OrderQuantity
INTO #StreamedData
FROM @Tmp s
INNER JOIN @purchase p
ON p.fruit = s.fruit
-- This unique clustered index will make the query sing.
CREATE UNIQUE CLUSTERED INDEX ucx_Helper ON #StreamedData (fruit, rn)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2015 at 4:10 am
ChrisM@Work (6/25/2015)
Yes, or this which looks a little cleaner:
SELECTrn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
max_rn = COUNT(*) OVER(PARTITION BY s.fruit),
s.*,
p.OrderQuantity
INTO #StreamedData
FROM @Tmp s
INNER JOIN @purchase p
ON p.fruit = s.fruit
-- This unique clustered index will make the query sing.
CREATE UNIQUE CLUSTERED INDEX ucx_Helper ON #StreamedData (fruit, rn)
I really appreciate all the help I've had on this. I've read about how indexes on temp tables are one of the benefits of a temp table. This is probably a silly question but if this code is going to be called frequently, a few times a second would creating an index each time be okay?
June 25, 2015 at 4:34 am
bugg (6/25/2015)
ChrisM@Work (6/25/2015)
Yes, or this which looks a little cleaner:
SELECTrn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
max_rn = COUNT(*) OVER(PARTITION BY s.fruit),
s.*,
p.OrderQuantity
INTO #StreamedData
FROM @Tmp s
INNER JOIN @purchase p
ON p.fruit = s.fruit
-- This unique clustered index will make the query sing.
CREATE UNIQUE CLUSTERED INDEX ucx_Helper ON #StreamedData (fruit, rn)
I really appreciate all the help I've had on this. I've read about how indexes on temp tables are one of the benefits of a temp table. This is probably a silly question but if this code is going to be called frequently, a few times a second would creating an index each time be okay?
There's no definitive answer to this. Some folks believe that temporary tables should rarely if ever be indexed. In this instance you are likely to find an improvement. You should always test, using a dataset representative of what you expect to have in production, and include the time taken for creating the index.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 3, 2015 at 6:39 am
Hi All,
I've added in a stock check which will override preference if all stock can be found in one location
CASE WHEN s.InStock >= p.OrderQuantity THEN 1 ELSE 0 END DESC
Which works fine for individual products , where ReserveFromShop > 0 code below.
DECLARE @Tmp TABLE (fruit VARCHAR(50), shop VARCHAR(10), preference INT, InStock INT)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop1',1 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop2',2 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('apple','shop2',1 ,5)
DECLARE @purchase TABLE (fruit varchar(50), OrderQuantity INT)
INSERT INTO @purchase (fruit, OrderQuantity)
VALUES ( 'orange', 2 )
,( 'apple', 4 )
--------------------------------------------------------------------------------------------
IF OBJECT_ID('Tempdb..#rankedData') IS NOT NULL DROP TABLE #rankedData;
SELECT rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY CASE WHEN s.InStock >= p.OrderQuantity THEN 1 ELSE 0 END DESC,s.Preference),
s.*,p.OrderQuantity
,max_rn = COUNT(*) OVER(PARTITION BY s.fruit)
INTO #rankedData
FROM @Tmp s
INNER JOIN @purchase p ON p.fruit = s.fruit
CREATE UNIQUE CLUSTERED INDEX ucx_Helper ON #rankedData (fruit, rn)
;WITH stockCalc AS (
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity, tr.max_rn,
ReservedFromShop = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.InStock ELSE tr.OrderQuantity END,
Outstanding = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.OrderQuantity - tr.InStock ELSE 0 END
FROM #rankedData tr
WHERE rn = 1
UNION ALL
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity, tr.max_rn,
ReservedFromShop = CASE WHEN lr.Outstanding > tr.InStock THEN tr.InStock ELSE lr.Outstanding END,
Outstanding = CASE WHEN lr.Outstanding > tr.InStock THEN lr.Outstanding - tr.InStock ELSE 0 END
FROM #rankedData tr -- this row
INNER JOIN stockCalc lr -- last row
ON lr.fruit = tr.fruit
AND lr.rn + 1 = tr.rn
)
SELECT * FROM stockCalc
UNION ALL
SELECT rn,fruit,'unassigned' AS shop,c.preference,c.InStock,c.OrderQuantity,c.ReservedFromShop,c.Outstanding, c.max_rn
FROM stockCalc c
WHERE rn = max_rn AND c.Outstanding > 0
ORDER BY fruit, rn;
DROP TABLE #rankedData;
But if i can find all fruit at one shop that should override all shop preferences and stock per fruit, so the fruit is not coming from multiple shops. In the output of the above code the orange should come from shop2 as this is where stock for the apple is.:crazy:
August 3, 2015 at 10:17 am
Okay this is the best solution I could come up with:
DECLARE @fruit_shop TABLE (shop NVARCHAR(10))
INSERT INTO @fruit_shop VALUES ('shop1'), ('shop2')
DECLARE @Tmp TABLE (fruit VARCHAR(50), shop VARCHAR(10), preference INT, InStock INT)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop1',1 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('orange','shop2',2 ,4)
INSERT INTO @Tmp (fruit,shop,preference,InStock) VALUES ('apple','shop2',1 ,5)
DECLARE @purchase TABLE (fruit varchar(50), OrderQuantity INT)
INSERT INTO @purchase (fruit, OrderQuantity)
VALUES ( 'orange', 2 )
,( 'apple', 4 )
DECLARE @tmp_new TABLE (fruit VARCHAR(50), shop VARCHAR(10), preference INT, InStock INT, purchase_qty INT)
INSERT INTO @tmp_new(fruit,shop,preference,InStock,purchase_qty)
SELECT stk.fruit,stk.shop,ISNULL(t.preference,9999),ISNULL(t.InStock,0),stk.OrderQuantity FROM
(SELECT shop,fruit,OrderQuantity FROM @fruit_shop, @purchase)stk
LEFT JOIN @Tmp t ON stk.fruit = t.fruit AND stk.shop = t.shop
DECLARE @Shop NVARCHAR(50) = NULL;
--ALL AT ONE SHOP WILL OVER RIDE
IF EXISTS (SELECT 1 FROM(SELECT shop, CASE WHEN instock-purchase_qty > 0 THEN 1 ELSE 0 END stock_flag FROM @tmp_new shop)r GROUP BY shop HAVING SUM(r.stock_flag) = COUNT(r.shop))
AND EXISTS (SELECT 1 FROM(SELECT shop, CASE WHEN instock-purchase_qty > 0 THEN 1 ELSE 0 END stock_flag FROM @tmp_new shop)r GROUP BY shop HAVING SUM(r.stock_flag) <> COUNT(r.shop))
BEGIN
SET @Shop = (SELECT TOP 1 r.shop FROM (SELECT shop FROM (SELECT shop, CASE WHEN instock-purchase_qty > 0 THEN 1 ELSE 0 END stock_flag FROM @tmp_new shop)r
GROUP BY shop HAVING SUM(r.stock_flag) = COUNT(r.shop)) r INNER JOIN @Tmp t ON r.shop = t.shop ORDER BY t.preference)
END
ELSE IF EXISTS (SELECT 1 FROM(SELECT shop, CASE WHEN instock-purchase_qty > 0 THEN 1 ELSE 0 END stock_flag FROM @tmp_new shop)r GROUP BY shop HAVING SUM(r.stock_flag) = COUNT(r.shop))
AND NOT EXISTS (SELECT 1 FROM(SELECT shop, CASE WHEN instock-purchase_qty > 0 THEN 1 ELSE 0 END stock_flag FROM @tmp_new shop)r GROUP BY shop HAVING SUM(r.stock_flag) <> COUNT(r.shop))
BEGIN
SET @Shop = (SELECT TOP 1 shop FROM @Tmp t ORDER BY t.preference)
END
--------------------------------------------------------------------------------------------
IF OBJECT_ID('Tempdb..#rankedData') IS NOT NULL DROP TABLE #rankedData;
SELECT rn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY CASE WHEN s.InStock >= p.OrderQuantity THEN 1 ELSE 0 END DESC,s.Preference),
s.*,p.OrderQuantity
,max_rn = COUNT(*) OVER(PARTITION BY s.fruit)
INTO #rankedData
FROM @Tmp s
INNER JOIN @purchase p ON p.fruit = s.fruit
WHERE (s.shop = @Shop OR @Shop IS NULL)
CREATE UNIQUE CLUSTERED INDEX ucx_Helper ON #rankedData (fruit, rn)
--SELECT * FROM #rankedData rd LEFT JOIN (SELECT DISTINCT shop FROM @Tmp)t ON rd.shop = t.shop
;WITH stockCalc AS (
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity, tr.max_rn,
ReservedFromShop = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.InStock ELSE tr.OrderQuantity END,
Outstanding = CASE WHEN tr.OrderQuantity > tr.InStock THEN tr.OrderQuantity - tr.InStock ELSE 0 END
FROM #rankedData tr
WHERE rn = 1
UNION ALL
SELECT tr.rn, tr.fruit, tr.shop, tr.preference, tr.InStock, tr.OrderQuantity, tr.max_rn,
ReservedFromShop = CASE WHEN lr.Outstanding > tr.InStock THEN tr.InStock ELSE lr.Outstanding END,
Outstanding = CASE WHEN lr.Outstanding > tr.InStock THEN lr.Outstanding - tr.InStock ELSE 0 END
FROM #rankedData tr -- this row
INNER JOIN stockCalc lr -- last row
ON lr.fruit = tr.fruit
AND lr.rn + 1 = tr.rn
)
SELECT * FROM stockCalc
UNION ALL
SELECT rn,fruit,'unassigned' AS shop,c.preference,c.InStock,c.OrderQuantity,c.ReservedFromShop,c.Outstanding, c.max_rn
FROM stockCalc c
WHERE rn = max_rn AND c.Outstanding > 0
ORDER BY fruit, rn;
DROP TABLE #rankedData;
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply