Presumably your "derived table" has a column that indicates which source system the data came from ?

There is a pretty generic pattern that can solve this, and it can be...

Forum search on keyword "Pivot":

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=238656#bm238751

Isn't this the exact same question as on your other thread ? Can we let this old thread rest in peace now, and continue the discusssion on your new thread ?

Replace table/column names with the appropriate names for your table.

Select Distinct A.*

From YourTable As A

Where Exists (

  Select *

  From YourTable As B

  Where A.FN = B.FN

  And   A.LN = B.FN

 ...

>>Is the index needed?

Every table should have a clustered index, so in that sense, yes.

The solution would seem to be to drop the index and make the Primary Key clustered. Or alternatively,...

>>You're right... I just dropped that in during some testing .. .forgot to take it out before posting. 

I'm now thoroughly confused.

The original question was why you couldn't filter on this...

In your COMPUTE, you have several itmes like this:  Sum(dt1).

The SUM() aggregate function expects a column name, but you are giving it a table name. It needs to be SUM(dt1.SomeColumnName)

But then you had a filter, looking for only the word 'Flag' ?

If the intention was to show some records as 'Flag' and some records as 'OK', why the filter...

You don't use expressions in the SELECT to set flags, if the only purpose of those flags is to filter.

Filtering is done in the WHERE, so place the filter expression...

Please look at the original SQL:

ConcStatus =Case

Notice the = ? That is old-style SQL syntax for assigning an alias to an output column in the resultset....

ConcStatus is a column alias, assigned to an expression in the SELECT portion. You can't reference this alias in the WHERE, ORDER BY or GROUP BY.

If you need it in the...

>>the total of the integer values from the LOS field

What datatype is the LOS column, and what does it typically contain ? What does "integer value" mean ?

Is it...

Append this to the 1st query ...

Union All

Select A.Product_id, A.Purch_Date, NULL, [repeat as many Nulls as you need]

From A

Where Not Exists (

  Select * From B

  Where B.Product_id = A.Product_id

 ...

http://www.sqldts.com/default.aspx?246

More info would be useful.

Why do you need a GUID ? Are you synchronising this data across multiple sites, or exchanging it with other external entities ? If not, just...