Youch.

First, I'd toss the query hints. Then, get rid of all the functions on columns in the JOIN criteria and WHERE clause. After that... who knows. I'm not even addressing temp tables or table variables yet, but those will have to get dealt with too. Sheesh. Stuff like that makes you nuts.

In general, when I'm forced to rebuild an insane query like that, I take it down to one table and then rewrite everything from scratch. The hard part is not simply tuning the query, but ensuring that the data being returned is the same (although, as written, I wouldn't trust a row coming out of the query).

Good luck.