It's only 238 lines. That's what, two printed pages, maybe three. We don't start talking about how evil a query is until it breaks at least 10 printed pages.

So, we have a view. That we join over and over. Indications are that view is also somewhat problematic. I love the hash match join on, did I count the zeros correctly, 130 billion rows. I'm sure no one is shocked to find that the optimizer timed out.

First question, what's this for? No one looks at 300 billion rows. And, as I'm sure you're figuring out, you can't really process 300 billion rows in a single step.

This is not a tuning project. This is a redesign project. Eirikur has it right (which is not abnormal).

Second question, is it 300 billion (or however many zeroes there are) rows or, on top of the very problematic T-SQL do you also have statistics issues?

I'm with Erik. Break this thing down to the smallest possible pieces and start rebuilding one join at a time, directly against the tables. Don't use that view ever again.

If everything else wasn't enough, pretty sure this will also lead to scans:

(CASE WHEN dbo.avoice_candidate.term_dte = '19000101' THEN NULL

ELSE dbo.avoice_candidate.term_dte

END IS NULL)

Could be wrong about that, but it hardly matters. You need to reconstruct this thing entirely AND, most importantly, understand what it's actually for, what it is intended to accomplish.