Yes that did correct the problem of teh syntax error. But after letting it run for 3+ hours I ended up canceling it.

Still trying to determine hoe to create a...

I tried your new query.

Got an error on line 20 saying:

Invalid column name 'X_AREA2'

We rebuild all indexes every night and also update statsistics with full scan every night.

I tried creating two individual views with schemabinding so I could add indexes on the views,...

SS,

Yes I did that already, made a query like you said. I should have given a list of indexes that are already present. But yes, that index was already there.

One...

GSquared, SSCrazy,

There is no need for me to carry this Post any further. The reason for this is because your last suggestion was the solution I was looking for.

For...

I understand completely. I will put together data for all 3 tables that you can load and give you what the results should be. This should make it a lot...

I tried that and it still returns nothing.

The param table always has a single row. It contains the number to match with of which rows to return. Each time a...

Because my Sort order is Binary, I need to capitalize everything as follows:

;with Main (CONTACT_WID, SOURCE_WID, OFFER_WID) as

(select Chf.CONTACT_WID, Chf.SOURCE_WID,

...

No I am sorry to report that your modification is a lot worse. It is suppose to return 150 rows, this new modified query return 1.7 million rows and the...

Your query is very fast and I noticed it is SQL Server 2005 only. That is okay, I don't have SQL Server 2000 any more.

I have attached the execution plans...

I apologize, please disregard my last post. I need to load "new" records into our OLTP database and run an ETL job to pull the new records into our OLAP...

Let me first say I am impressed with the timing of this query, it runs in 21 seconds. But unfortunately, it returns nothing.

I appreciate your effort on this.

After removing the second word "JOIN" this query runs in 5 seconds and returns teh value 92377906

The original query that runs in 51 minutes is:

SELECT

CHF.CONTACT_WID , COUNT(DISTINCT CHF.SOURCE_WID) , COUNT(DISTINCT CASE WHEN POH.OFFER_WID IS NULL THEN 0 ELSE POH.OFFER_WID...

Yes Gus, this does return teh 1.8 million rows that is expected. Unfortunately it takes 21 minutes longer than the original.

A sample of the output is provided below:

5184773046

1880734317

192276711

5582125457

96377744

1794433418

11038294572

10483231119

17512101020

1751216382

Again, this...