bugg (8/28/2015)

---

Stephen Beaven-479566 (6/17/2010)

---

I have a SSIS package running on 2 of our live servers (Windows Server 2003 Enterprise x64 Edition SP2) which creates an excel spreadsheet and then compresses...

Stephen Beaven-479566 (6/17/2010)

---

I have a SSIS package running on 2 of our live servers (Windows Server 2003 Enterprise x64 Edition SP2) which creates an excel spreadsheet and then compresses it...

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...

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...

ChrisM@Work (6/25/2015)

---

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...

Something like this:

SELECT *,max(rn) over(partition by fruit) AS max_rn INTO #StreamedData

FROM

(

SELECT...

Very cool 🙂

I've amended the code to pull in unassigned order quantities similar to spaghetti's output 🙂

DECLARE @tmp...

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)...

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...

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...

🙂 not fruit but selling other products, I...

Yep spotted it , just testing it now .. You are a legend!!! thank you for...

Yep it slows right down when there are 1000's at each shop. :laugh:

Do you think if I split the...

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...

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...