Joie Andrew (5/20/2010)

---

halifaxdal,

I don't think that Lutz was trying to give you any attitude at all. I think he was simply trying to identify what you have already...

Please read and follow the instructions given in the article referenced in the first link in my signature.

This will allow us to work on a solution instead of trying to...

I've seen that too but didn't feel like a human search engine today......

I second Stefan regarding not to trust the % measures from an execution plan.

Especially if the query itself doesn't take that much time (like in your case).

The main reason is...

What links/hints/information did you get using a simple Google search?

It seems like there is a load of information out there...

Your query itself seems to be valid in terms of syntax.

You should narrow the problem down in SSMS and build the dynamic string later.

My guess would be there are either...

Something like this?

SELECT

Y.value('@Guid[1]','char(37)') AS Guid_,

Y.value('(.)[1]','varchar(100)') AS Property_

FROM @XML.nodes('Root/Property') X(Y)

It depends.

If you want to store the integer value converted to character then you can't, since the value '94' has two character.

But if you want to store the ASCII character...

Based on a numbers (or tally) table I'd use the following logic:

;

WITH cte AS

(

SELECT

n,

(ROW_NUMBER() OVER(ORDER BY n)-1) % 5 +1 AS c,

(ROW_NUMBER() OVER(ORDER BY n)-1) /...

This was actually my first thought before I stumbled over the OP's requirement at the end of his first post:

I will also need to run this query on a SQL...

Jeff Moden (5/18/2010)

---

lmu92 (5/18/2010)

---

Would something like the following help?

SELECT * FROM

(

SELECT 'Smith John' AS lastname UNION ALL

SELECT 'S#ith Jane' UNION ALL

SELECT '\ Jim' UNION ALL

SELECT 'Anderson Ken' UNION ALL

SELECT 'Smith-Barny...

bteraberry (5/18/2010)

---

That wouldn't be hard to check for if that's part of the requirements. What he said was

if a "re-open" occurs followed at some point by a "ready...

Lowell (5/18/2010)

---

damn Lutz; your's looks much better than mine, so i decline to post it 🙂

very nice job.

blade, if you look at Lutz's contribution, it's doing all your updates as...

Nice and clean solution.

But if you have the following scenario of two 'Re-open' without a 'Ready for build' in between, you might get wrong results:

...

Here's a Q&D (quick and dirty) CTE approach using your INSERT statement to define the CTE.

The final SELECT actually uses a bunch of CASE statement to cover your various...