Does this do what you need?

;with CTE (ConsultantID) as

(select consultantid

from volume

where deactivationdate between @startdate and @enddate

and active = 0

group by consultantid

having sum(purchaseamount) > 1000)

update #Temp6

set points = points + 1

grom...

d.col1 = replace(c.col1, '-','')

bitbucket (4/3/2008)

---

Here we go again ....

For Steve and others - might I suggest asking a few users to form a panel to check the questions BEFORE they are published...

I modified your test data as follows:

create table prod (pid int primary key, name varchar(10), pdesc varchar(max))

insert into prod (pid, name) values (1,'prod1')

insert into prod (pid, name) values (2,'prod2')

create...

Soundexes for "Gardeners" and "Garden'" are the same (G635), but soundexes for "Botanicals" and "Bot'icals" aren't the same (B352 vs B300). It might help narrow down the similarities, but...

If you want to change the data:

update dbo.YourTableName

set YourColumnName = replace(YourColumnName, '-', '')

where YourColumnName like '%-%'

That'll be better than having a function in your Join clause, in terms of performance....

PW (4/2/2008)

---

I believe it's confusion regarding the data column in the table, versus the column alias in the query:

SELECT Convert(Char(15),LastUpdate,106) as LastUpdate..

...

ORDER BY LastUpdate desc

The 2 "LastUpdate" references above...

Jeff Moden (4/2/2008)

---

Antonio,

Your solution doesn't work with the test data I created. Please post your test data generator and the result times you got.

Gus, you're splitting out more than...

Jeff Moden (4/1/2008)

---

GSquared (4/1/2008)

---

select dateadd(month, 1, cast(enddate + '01' as datetime)) - 1

from dbo.sasimp

Should give you what you need. If it gives you an error about converting enddate to...

Jeff Moden (4/2/2008)

---

At a high level, yes... You split on the "&" instead of the "=" which...

Looks like this one got set up incorrectly.

(I have to confess I wasn't as courteous as Antonio, and posted my tests on Numbers/Tally vs XML on the original thread. That's here: http://www.sqlservercentral.com/Forums/Topic478171-338-2.aspx)

Just in case the Cross Apply is somehow more efficient than a straight up join (which is ridiculous on the face of it), I also tested:

ALTER function [dbo].[StringParser]

(@String_in varchar(max),

@Delimiter_in char(1))

returns...

XML Function test:

ALTER function [dbo].[StringParserXML]

(@String_in varchar(max),

@Delimiter_in char(10))

returns @Parsed table (

Parsed varchar(100))

as

-- This one is faster than StringParser, but it doesn't correctly handle

-- XML-specific characters, such as "<" or "&". ...

I created test data as follows:

create table ParserTest (

ID int identity primary key,

String varchar(max))

go

insert into dbo.parsertest (string)

SELECT 'Subject=Drilling&Category=Drill Header&Status=NO&IPPDM_Count=94356'

+'&Well_Count=84353&Zone_Status=NO&Prod_String_Count=95249&R_Master_W_Count=94353&'

+'BeginDate=2/3/2008&EndDate=2/5/2008'

from common.dbo.bignumbers

insert into dbo.parsertest (string)

SELECT 'Subject=Zone&Category=Zone Header&Status=YES&IPPDM_Count=94356&Well_Count='

+'94356&Zone_Status=NO&Prod_String_Count=95249&R_Master_W_Count=94356&AddlZoneInterest'

+'=107674'

from common.dbo.bignumbers

Then I tested a...