Duh!!!!

It was the table name alias (the bit in the bold) I was missing!!!!!

Thank you sanmatrix for jogging my memory!

Just reporting back......

OK, the temp table route runs in less than 2 seconds......an acceptable speed given the amount of data and processing involved (of which I only showed you a...

mrpolecat (7/7/2010)

---

If your supplier data is unique the you don't need distinct when looking for records that don't match since there should only be one of each to begin with....

steveb. (7/7/2010)

---

Have a look at the actual execution plan, this will show you where the performance bottlenecks are. Post them here if you need help analysing them.

Also...

Thanks for all your help.

I've adopted a version of MM's solution, even though he wasn't happy with it!

Performance isn't really an issue for me though, as the code runs as...

mister.magoo (6/28/2010)

---

I am a bit busy now to do you a "good" answer, but if you just want something that works but is bad....

SELECT tool,

(SELECT TOP 1 wos_ordref

...

mister.magoo (6/28/2010)

---

Try this

SELECT tool AS product, wos_ordref, wos_startdate

FROM @xTOOLS

OUTER APPLY (SELECT TOP 1 wos_ordref,wos_startdate

FROM...

OK, I've spotted what I did wrong. I've obviously been staring at the code for too long because I've become blind to it!

It was simply that the .LockType was missing...

I think I've managed to fix this myself. If I add ABS to the code, it obviously removes the sign from the returned value and all seems well.

My new code...

Hello again,

I've re-done it your way! It just seems more elegant and efficient to add the CAST to the construction of the @cmd variable, rather than parp around converting the...

Hi David,

Many many thanks for your contribution. It led me to the answer although I still had some problems at first.

To cut a long story short, I converted my integer...

Sorry!

I wasn't sure which category it fell into.

Lesson learned though!

Hooray! Easily pleased......me????

OK, it's the does not equal signs that are the problem.......so........

IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL

AND CONVERT(CHAR(10),[Date Processed], 103) <= CONVERT(CHAR(10), GETDATE() - 1,...

Nope....didn't work. broken up onto two lines maybe?

IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL

AND CONVERT(CHAR(10),[Date Processed], 103) 0