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.

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

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

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

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

Updated the top,...

DELETED

Mark Cowne (5/3/2012)

---

Join to a numbers/tally table

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE

FROM @SPLITROW s

INNER JOIN master.dbo.spt_values t ON t.type='P'

AND t.number...

SQL Check is showing this for me too but no transactions are in an open state is this a VMWARE issue?

This is on SQL Server 2012 Standard

Thanks

B

Thanks Shafat unfortunately the event viewer isn't setup to store...

php_guy (10/28/2014)

---

I have an Access DB. in that DB there is a table which has a field which contains a switch statement. I want to carry this function over to...

Yep... this is what i mentioned in my original post.;-)

GilaMonster (10/28/2014)

---

Rather focus on making your app injection-proof.

They wouldn't have used public for this, they'd have been using whatever the application's account is, since it's a query exploiting the application's...

I thought as much. Is there a way to deny the public role read access to system tables?

I created a covering index and included all columns being returned in the select statement, this has stopped the Deadlocks. It doesn't feel right just including all columns in the...