You can also do it with a single result set.  Which performs better would depend, so you'd have to test that out.

This method would also be much easier if you...

Great, glad it helped.  Many thanks for the feedback.

I'm not sure PIVOT can give you the results you want.

Maybe try this instead?:

SELECT 
    COALESCE(BBBB.STOCK_CODE, CCCC.STOCK_CODE) AS STOCK_CODE,
    COALESCE(BBBB.MNEMONIC, CCCC.MNEMONIC)...

D'OH, obviously there is an mbi column, since it's directly referenced in the query.

The point here is that, in a subquery, if you reference a column that exists in a...

• This reply was modified 2 weeks ago by ScottPletcher.

Is there an MBI column in table "edi.dbo.PEC_RGT_EDI_834_Inbound_Exception"?

If so, the second query works because it is comparing xx.MBI to itself, which will always be equal (unless MBI is NULL, oc).

x.MBI...

My thoughts on this:

(1) Decide whether you want to include nvarchar or not.

(2) Gen a list of the tables to be changed: then, if you need to, you can custom...

I identified the changes I made to the original code using /*--<<--*/ on/around them.  This code should also perform better.

SELECT
sa.partid AS ALLOC_Part,
sa.quantity,
sa.ordertype,
sa.worksorderid,
sa.reference,
sa.stockvalue,
apm.partdesc,
soi.orderid,
soi.itemnumber,
soi.partid AS SOI_Part,
so.traderid,
t.name,
u.name As SalesRep,
sub.Max_Batchvalue AS...

You could also use Brian Gale's approach and use a proc to generate all the code for you.  Personally I would stick with dynamic SQL, but the other is an...

Columnstore won't gain you much (other than perhaps some disk space) since you are reading all the columns.  Personally, I would just use page compression.

Absolutely agree with analyzing page compression for the table.  It could be a huge help.  You can use:

EXEC sys.sp_estimate_data_compression_savings 'dbo', 'table_name', NULL, NULL, 'PAGE'

As  you noted, the nonclus index is...

I thought it was more complicated than that because a CHAR(13) should also count as a "space" as far as splitting words?!  Perhaps not, I guess it depends on exactly...

IF UPDATE(CreditLimit)

That approach seems preferable to me, unless you really have to use COLUMNS_UPDATED()

If the quirky UPDATE works, can you modify the original table to include the new calculated column?

Let me ask: once a value has been calc'd, would it ever change?  In...

Please provide data in usable format: CREATE TABLE and INSERT statment(s).  That is way more useful to us than a "picture" of data.

Would need to see row counts to better understand the query.

How is the Departments table clustered?  In general, again without knowing any details at this point, DepartmentID first followed by...