mike.dinnis (8/12/2013)
The main query (from which this was the identified 'slow' bit) uses; emp_num, trans_type, a_hrs, qty_moved, wc and CreateDate in the returned fields; job, suffix, wc, oper_num and trans_num in the JOINs and trans_class, posted in the WHERE clause.If it would be helpful I can return the whole statement, I was trying to focus on the 'slow' bit in my initial post.
It's worth a look.
It may well be worth investigating ROW_NUMBER because a large proportion of the output rows have a value from the subquery and you're having to scan the whole table (clustered index) in any case. If there’s a suitable index to get around the sort usually required by ROW_NUMBER then you could be on to a winner. It would be a whole lot easier for us if you could mock up some sample data. A single set based on job & suffix would do.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden