I hear what you guys are saying. It's mostly trying to talk the reporting guys out of attempting to handling EVERYTHING within the SQL stored-procedure and encourage them to learn more about SSRS so they can take advantage of the power it offers to do the majority of their aggregation(s), presentation, and such.
Regarding the removal of the query hints - I assume you refer to the locking hints? I normally would agree (as in my previous experience I've never ever used them, however at this company most of the locking is controlled via the vendor application, and queries like this run against a transaction replicated database - when they're not included at the reporting level, they get massive deadlocks during the daily reporting runs.
I've entertained using different isolation levels but am not overly familiar with the overhead that may be associated with them (it's way down on the to-do list).
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).
This is the crux of my headache - it's insanely difficult for me to match the output!
Thanks for the comments, I needed to vent...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience