Dwain,

I bow before the majesty of your CASE / GROUP BY logic. I couldn't figure out how to do it without a temp table.

As my neighbor would say, "that's slicker...

Moden's string splitter (or any string splitter) can be used in this manner:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION TVF_2col

(

@col varchar(8000)

)

RETURNS TABLE

AS

RETURN

(

select a.item ky,b.item val from

[dbo].[DelimitedSplit8K](@col,'|')...

For some reason you don't need the hint if you do it like this.

DECLARE @entityIds XML

SET @entityIds =N'<?xml version="1.0" encoding="utf-16"?>

<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<int>112350</int>

<int>100886642</int>

</ArrayOfInt>'

---INSERT INTO TEMP TABLE

SELECT Tbl.Col.value('.', 'int') AS val

into #entityIdList

FROM...

And one using CASE

select Id, ProductName,cMin from

(

select ID, ProductName, case when cost1<cost2 then cost1 else cost2 end c1,case when cost3<cost3 then cost3 else cost4 end c2

from table_name outer apply (select...

Try this TVF. Make sure there are indexes on loaddate in both order and product, with covering columns for ordernuber and product columns.

CREATE FUNCTION tvf_loadDate

(

-- Add the parameters for...

Now, the thing I'm struggling with since my concatenated field will never have the same number of values and could look something like this in addition to what I have...

jay-h (10/29/2013)

---

lnardozi 61862 (10/29/2013)

---

My standard reply when they mispronounce my name the third time is, "It's okay, I know stupid people can't pronounce it."

Whip THAT one out in an important...

My standard reply when they mispronounce my name the third time is, "It's okay, I know stupid people can't pronounce it."

Whip THAT one out in an important meeting and see...

No, it's just the script. I figure if you don't already have a CLR library, you probably aren't interested anyway. As far as performance goes it should be pretty linear...

I wrote a little CLR stored proc that pivots anything by date. Should be released in the scripts section on Halloween!

Louis

Just a thought - category1 thru category5 could just be computed fields. You already have all the information you need in Score.

If one query is generating several hundred worker threads that really sounds like you have MAXDOP set to 0 (the default). Since you know the problematic query apparently, just take...

Parentheses

(SELECT ISNULL(CboValuesTranslations.cboValueTranslationName,ISNULL(CboValues.cboValueName,'')) as Name,

cboValues.cboValueSqlId as SqlId, CboValues.cboValueIncId as IncId,

LTRIM(STR(cboValues.cboValueSqlId))+'-'+LTRIM(STR(CboValues.cboValueIncId)) AS KeyId

FROM ListsOfCboValues INNER JOIN CboValues ON ListsOfCboValues.listOfCboValueSqlId = CboValues.listOfCboValueSqlId AND ListsOfCboValues.listOfCboValueIncId = CboValues.listOfCboValueIncId AND CboValues.isDeleted=0x0 LEFT OUTER JOIN CboValuesTranslations ON...

When in doubt, tinker. Tinker with what you are allowed to change (which doesn't sound like much). Can you set MAXDOP to 4? MAXDOP 0 can be a killer as...

TRY/CATCH statements are used to rollback a transaction if an error occurs in processing. The COMMIT goes at the end of the TRY and the ROLLBACK goes inside the CATCH....