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: