Thanks so much for your help Eugene, Chris, Sergiy. I've rewritten the query according to your suggestions and it now runs much more efficiently. I learned a lot in the process, you guys are great teachers.

Just to tie up a few loose ends that were discussed:

I wasn't sure if I should put COALESCE in the ORDER BY when I first wrote the query. In my now rewritten query I've changed that so that the ORDER BY corresponds to items in the SELECT list.

Those are columns related to notes on the data which I'm excluding from my results with this line:

IndicatorRecordContainsAlphanumericNoteRatherThanData = ''

They are needed as part of the key for extracting notes on the data. This happens in a separate query.

So to summarize, the following sped up my query a lot:

-- As per Eugene's suggestion in his first post, I changed FieldIDNumber column from varchar to numeric. Then I removed brackets from the filter relating to FieldIDNumber so that my server could compare numbers rather than varchar data.

-- While doing that I noticed another bottleneck relating to a filter which I hadn't included in my first post. Originally instead of the filter:

IndicatorRecordContainsAlphanumericNoteRatherThanData = ''

,I was using a filter based on a different column that was not part of the primary key. That other column also tells me whether the record is a note or not but since it is not part of the key, using it was slowing my query dramatically. Changing that sped things up, which I did prior to posting the execution plan, complete query and DDL today.

I think the above two items were the primary reasons for the hit to CPU that I mentioned in my first post. After the above changes the primary bottleneck moved to storage i/o.

-- Then as per suggestions from Sergiy, Chris & Eugene, I changed to a crosstab query to eliminate the repeated joins on the same table. That resulted in a further significant speed boost.