Absolutely agreed.

Now the OP can decide which scenario he's dealing with and he has the "best-fit solution" for either one. Can't be any better, right?

WayneS (9/2/2010)

Based on this, where the work order ID is the 2nd "field" in this string, I would suggest :

declare @test-2 varchar(100);

set @test-2 = 'W~W51247~0~1~0';

select Item

from dbo.DelimitedSplit8K(@test, '~')

...

You could cerate an XML Schema collection and use it to make your xml variable a typed variable.

For details see BOL (BooksOnLine, the SQL Server help system), section "typed XML"...

How about taking a look at the CrossTab article referenced in my signature?

It might help you to get the data pivoted.

I'm not sure who you refer to with "Just someone...

Assuming Work Order type will always be char(1) (based on the column definition) and ID will always be char(6) I'd go with the substring method Cory suggested.

Depending on the number...

I've never been to PASS Summit but I'd love to go some day.

Regarding the value of it:

Don't get me wrong, but the value of the Summit for you is influenced...

WayneS (9/2/2010)

Probably has something to...

But...

DECIMAL(19,4) (or NUMERIC(19,4)) and MONEY are not, as I just googled...

Here's a sample (source: http://www.eggheadcafe.com/software/aspnet/33139628/interest-calculation.aspx):

DECLARE

@m1 MONEY,

@m2 MONEY,

@m3 MONEY

DECLARE

@d1 DECIMAL(19,4),

@d2 DECIMAL(19,4),

...

Money is actually represented by DECIMAL(19,4). So, if you want to add another decimal digit you'd need to change the data type to DECIMAL(20,5), if you want to make sure...

Since I don't support cheating I decided to remove my solution from the thread Lowell mentioned. I'm sorry for all those who might have found it useful in a more...

I don't think that's a question for a forum.

You might want to call a local consultant to help you improving performance.

Other than that: use SQL Profiler to identify long running...

Your question is not really clear...

What do you mean by "If this much is sufficient then its ok else..."??? What determines "sufficient"?

Also, please clarify what SQL Server version you're using....

Glad I could help 😀

@Bezan:

Would you mind adding your requested result based on your sample data so we have something to compare with?

Irobertson does an excellent job by continuously providing answers to every lpieco...

The WITH clause in this scenario can be seen as a subquery, just easier to read. So it's not available for the next statement.

In order to use it for more...