How to optimize this query

[mike01]

Please don't triple post. This is also here

How to write this query simplerer

I see 2 queries, but the second has a union all in it. When you say simpler, do you mean because it's so long? We don't know what the schema looks like, what the execution plan is or what the real issue is