rgp151 (12/8/2016)
Here is a plan where its just the query encapsulated in SELECT * FROM ()Remember, if I run just the part inside the outer nesting it runs in under 5 minutes. I just left it running last night as described above and it ran for 23 hours without finishing.
Thanks
Edit: I also included an execution plan without the encapsulation. This is when the query runs in <5 minutes. I can't see a difference between the two.
"Showplan Comparison" indicates differences in the result operator alone (the SELECT operator). I suspect that one of these plans might not be the plan which was executed when you performed your timings. Also, does the non-encapsulated query have OPTION (RECOMPILE)?
In any case, with so many copies of a 5.5 million row table littering the plan, it's not going to win any races.
The reason I suggested commenting out the COUNT(DISTINCT... parts is that the remaining columns in the SELECT could probably be evaluated with a couple of simple aggregations and a single scan of the table - although you might have to perform a preaggregation to provoke the optimiser into generating such a plan. I'd tackle the COUNT(DISTINCT... parts as a separate exercise then merge the results, but make sure you really, REALLY need the DISTINCT, because it's hurting.
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