select t.*, n.l.value('.','VARCHAR(20)') as DesiredValue

from @temp t

CROSS APPLY ErrorData.nodes('/root/*[local-name(.)=sql:column("ErrorCol")]') n(l)

ColdCoffee (9/29/2010)

---

Mark-101232 (9/29/2010)

---

Maybe this?

WITH CTE1 AS (

SELECT ID,Value,

ID-ROW_NUMBER() OVER(PARTITION BY Value ORDER BY ID) AS rn

FROM @SourceTable),

CTE2 AS (

SELECT ID,

...

WayneS (9/28/2010)

---

SSMS 2008 - Multiple Server query question.

When I open up a normal query window connected to just one server, I can drag a file onto it, and a new...

Maybe this?

WITH CTE1 AS (

SELECT ID,Value,

ID-ROW_NUMBER() OVER(PARTITION BY Value ORDER BY ID) AS rn

FROM @SourceTable),

CTE2 AS (

SELECT ID,

...

SELECT DENSE_RANK() over (order by tref) Pareto,

tref.value('@costObject','sysname') [CostObject],

tref.value('@abcGrouping','sysname') [abcGrouping],

tref.value('@classGroupingId','sysname') [classGroupingId],

tref.value('@attributeType','sysname') [attributeType],

tref.value('@attributeName','sysname') [attributeName],

tref.value('@operation','sysname') [Operation]

FROM @xml.nodes('declare namespace d="http://Equazion.Reporting.Model/Paretos.xsd";

/d:ParetoDefinition/d:Paretos/d:Pareto

[not (@abcGrouping)]') as T(tref)

WITH CTE AS (

SELECT ClientID,SubClientID,DriveLetter,FreeSpace,

ROW_NUMBER() OVER(PARTITION BY ClientID,SubClientID ORDER BY FreeSpace,DriveLetter) AS rn

FROM #SerDriveInfo)

SELECT ClientID,SubClientID,DriveLetter,FreeSpace

FROM CTE

WHERE rn=1;

steve-893342 (9/22/2010)

---

Shouldn't that...

SELECT Name,

SUM(CASE WHEN DATEPART(month,saledate)=1 THEN cost ELSE 0 END) AS Jan,

SUM(CASE WHEN DATEPART(month,saledate)=2 THEN cost ELSE...

Still not sure about NULL TermDate, is it later or earlier than non-NULL?

See if this works

WITH CTE AS (

SELECT

ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,

PreviousFileDate,...

Maybe using NTILE

with cte as (

select Guestid,Fname,Lname,Date,employeeid,

ntile(3) over(order by Date) as grp

from tblGuest

where employeeid = 1

)

update cte

set employeeid = case when grp=1 then...

More results attached, this time on a server.

Also SQLCLR figures are this

Tally:

Table 'Tally'. Scan count 10000, logical reads 30000, physical reads 0

Table 'CsvTest'. Scan count 1, logical reads 774, physical...

Paul White NZ (9/19/2010)

---

Mark-101232 (9/19/2010)

---

The difference on mine is far less pronounced

Tally:

Table 'Tally'. Scan count 10000, logical reads 30000, physical reads 0

Table 'CsvTest'. Scan count 1, logical reads 774, physical...

Paul White NZ (9/19/2010)

---

For anyone wanting to see how a SQLCLR implementation compares to the other tested methods on this dataset, here's my slightly-tweaked version of Adam Machanic's string splitter:...

It's not really clear what your rules are here. Do you want the latest CaseNumber1 based on TermDate regardless of TermActionCodeID. If so, then this should work

WITH CTE AS (

SELECT

ID,...

Hi Jeff,

Results attached for my creaky Fujitsu Amilo laptop running Vista Home Premium, 2GB RAM, Intel Core 2 Duo

with SQL Server 2008 Express R2.

Query took 27 mins 49 secs to...