Check the compatibility level of the db. Compatibility needs to be at least 90 for ROW_NUMBER() to work.

Hmm, interesting. I thought the InspectionId was a "primary key", and therefore unique. Sorry, I'm confused now about the data structure and relationships.

From what you've described, I think this will select the rows you need. Btw, I hope the table isn't clustered on that guid, even though that is the PK.

Edit:...

No, I don't recall anything like that, not on newer versions. Maybe under SQL 6.5, which I never used. Perhaps under SQL 7.0, which probably didn't defer the...

Jeff Moden (9/9/2015)

It looks like someone may have followed the old idea of adding one index to each column that could ever be used in a WHERE clause and that's...

Check the compatibility level of the dbs. If that is too low, you'll get that syntax error, since earlier versions of SQL didn't allow that type of reference.

Edit: OOPS,...

Why do you refuse to create a clustered index??

Since you have Enterprise Edition, you can create the index online so it will barely, if at all, affect other users.

Drop all...

Quite right, sorry, left that table out of the outer query. Also forgot to check the CollectorCode in the inner query, I've added that back as well:

SELECT

H_Totals.David

, H_Totals.DavidCt

,...

I don't see anywhere that the dbo.Company table is being used, so I removed it.

I also removed the multiple JOINs, although I did not add a GROUP BY...

You could partition the table, but it's likely overkill in this situation. All you probably need is the best clustered index and only the nonclustered index(es) you actually need,...

There are two main reasons it's taking so long:

1) no clustered index

2) too many nonclustered indexes

My best guess is that Trx_Date should be the clustered index. But we need...

I don't use MPs either.

But check for missing dbs. For example, maybe a db was moved or renamed.

There's a simple rule for this:

NEVER use ISNULL() in a WHERE or JOIN clause.

You can easily code around it, and it could cause performance issues. Even if there's no...

SELECT first_appointment.*, next_appointment.*

FROM (

SELECT customer#, MIN(appoinment_date) AS appointment_date

FROM table_name

GROUP BY customer#

HAVING MIN(appointment_date) >= '20140101'...

You have at least one rule missing. For example, in

Text1=

'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'

The first A not followed by a B, C or D is "AGENDR". Why is that "A" excluded?