I only submitted my alternative because I don't like to use OR conditions in JOINs. They usually prevent the optimizer from using any supporting indices for the join.

kevin.bullen (4/8/2008)

---

I tried toggling the SET CONCAT_NULL_YIELDS_NULL setting in a query window.

SET CONCAT_NULL_YIELDS_NULL ON I get:

Msg 242, Level 16, State 3, Line 4

The conversion of...

that's probably due to SET CONCAT_NULL_YIELDS_NULL being OFF in your SS2K5 db/session. when set ON, null + '/01/'+ null will result in null (and cast/convert properly) rather than '/01/'.

Art,

You've got way too much going on here. You should simplify it.

'PORSalesPoints' = CASE

WHEN SUM(PurchaseAmount) > 15000 And NACDATE <= '06/30/2008'

...

don't thank me... thank jeff m. he's the one that did it. 😀

i just filled in the blanks for your specific statement.

:mtassin

sorry about the typo. see revised post.

this quick and dirty function works well for text lists.

create function [dbo].[fListToItems]( @list varchar(max), @delim varchar(max))

returns @returnTable table

( item varchar(255) not null )

as begin

declare @xml XML

set @xml = '...

you've already got a group by, so there's no reason to use DISTINCT. the statement below will only have 1 row per unique combination of t.ConsultantID, AchieveTitle, AchieveLevel, TeamSalesLevelAmount,...

you'll need to post more context, because your insert statement does not produce an error.

select 0 as ID_PRODUCT, 0 as NM_TOTAL into #tempTotal where 1 = 2

select 0 as ID_PRODUCT...

adressin (4/7/2008)

---

- PROTECTED_OBJECT_TYPE can be: 'property', 'department', or 'entity'

- if PROTECTED_OBJECT_TYPE is 'property', the PROTECTED_OBJECT_ID refers to a PROPERTY.PROP_ID (not shown for brevity), and the same is true for 'department'...

that's a really good hack Jeff. by omitting an alias on the column and using path(''), the result isn't even XML but it's darn useful! and much more...

Lisset (4/7/2008)

---

Hi, I have the follow script:

select x.guia

,(select atrb_cdg from atributos_suite ats where ats.clhl_cdg=g.clhl_cdg and atrb_cdg in ('CHRBK','FRDE','PFRDE')) atr_guia

from Guia

and I need convert the subselect in a string, by example...

off hand i would say the problem is the group by.

you're including every date in the group by but i think you want to omit them and wrap the titles...

where c.invoicedate < dateadd( month, -6, getdate() )

btw, the subtitle for your post was "Find invoice dates over 30 days old". to do that, use dateadd( day, -30,...

pommguest99 (4/7/2008)

---

Gsquared / Antonio,

Thanks for being patient wiht me.

Here is what I came up & it is throwing a syntax error. My another question is how do I run this...