Note how the duplicates in the column Tdl_Id generate more rows with your code. You can also check the difference between execution plans (which correlates directly to the code).

Based on the logic that I can understand from your query, you wouldn't even need derived tables. Look at how I'm posting DDL and sample data, you're supposed to post...

There's a different method but with up to 200 databases the validations can become complex and the improvement is unnoticeable. This is one of those occasions where a cursor is...

You mean like cross tabs?

http://www.sqlservercentral.com/articles/T-SQL/63681/

You can try using common table expressions.

However, your example shows that you're using the same table over and over again joined by the same key. That's only a recipe for...

This is an idea on how to do it. It's not perfect as the NULLS will be sorted first. I'm sure that you'll be able to handle that.

DECLARE @RowCount ...

Why do you want to count before truncation? That would only slow you down. Additionally to that, you can't use variable values as table names unless you use dynamic SQL....

It seems to me that you don't need a cursor (or worse, nested cursors). This could be accomplished with direct queries.

However, as Steve told you, we need DDL and sample...

Yet another version taking the advantage of preaggregation used by Jason and single table scan from Michael.

WITH preAggregation AS(

SELECT WIP_master.uompScheduleNumber

,CASE WIP_master.uompScheduleColor

...

You can read the following articles about cross tabs:

Part 1[/url]

Part 2[/url]

You might need to use ROW_NUMBER() to create "constant" values and MAX() to "aggregate" the data.

mhildebrand (12/12/2014)

---

One voucher paying several vendors and invoices is quite normal. The problem is that you have 2 tables, one defines a relationship between voucher, vendor and invoice and the...

mhildebrand (12/12/2014)

---

Luis, in our environment we may have 1 voucher that pays several vendors and several invoices among those vendors. The reason this all started is I wanted to...

Help me out here. You posted several rows with the same voucher number on both tables. That would create a cartesian product and give incorrect results. Is this correct? You...

mhildebrand (12/12/2014)

---

This is not the kind of response a new person on this forum finds helpful, you are basically giving me the finger when I thought I was giving you...

You just need a single CASE.

SELECT CASE

WHEN PT.datatype = 7 AND MVA.DateTimeValue IS NOT NULL

...