busraker (8/28/2013)

---

Your solution would run much faster (my code must test every previous day in order to determine the proper sort order, and you rely on a case statement) but...

Grant Fritchey (8/28/2013)

---

Koen Verbeeck (8/28/2013)

---

JLMayes (8/28/2013)

---

TravisDBA (8/28/2013)

---

Agreed, but let's make sure ALL the facts are uncovered first before calling anyone out. Otherwise, it just turns the whole situation into a...

And again, just in case it was missed the first time around.

Can you post the actual execution plan, Kevin? As a .sqlplan attachment. It's the source of information for anyone wanting to optimise a query. There are plenty of folks around...

SQL Server is turning the OJ into an IJ because of the inner-joined "trail". You can get around it by changing the position of the ON clauses like this:

SELECT P.policy_internal_id,

RTRIM(P.contract_main_id)

+...

This returns incorrect...

It's to support correlated subqueries like this:

select *

from test1 t1

where t1.firstname1 in (select t2.firstname2 from test2 t2 where t2.firstname2 = t1.firstname1)

and is equivalent to this:

select *

from test1...

Write a SELECT statement joining the two tables which outputs the PK of the table you want to update, the existing domain value, and the value you want to change...

Revolting and unacceptable. But what has been done? What message will be sent out to this and other groups if nothing is done?

Not bad at all, busraker. Here's a slight mod which I think is a little easier to figure out, but the logic I think is the same as yours:

;WITH CountedData...

Here's another way:

SELECT p.ProductID, MAX(x.OrderList)

FROM @OrderDetail p

CROSS APPLY (

SELECT STUFF(

(SELECT ',' + CAST(o.OrderID AS varchar(5))

FROM @OrderDetail o

WHERE o.ProductID = p.ProductID

ORDER BY o.OrderID

FOR XML PATH(''))

, 1, 1, ' ')...

John's correct, here's another way:

SELECT

col1 = MIN(CASE WHEN t.value = x.min_value THEN value ELSE NULL END),

col2 = MIN(CASE WHEN t.value = x.min_value THEN t_stamp ELSE NULL END),

col3...

You haven't explained any rules for the assignment of 'flavour'. Will a guess be sufficient?

T.Ashish (8/27/2013)

---

Hi Chris,

Thanks again for your help.

I could not see any performance difference with/without function. I have pasted the function for you. Meanwhile I'm looking into third point you suggested.

CREATE...

A few comments.

1. This personalisation:

on wo . bu_id = bu . bu_id

with unnecessary spaces either side of object delimiters, makes the code irritating to read and will put folks...