Don't get confused into thinking that a CTE materialises the query inside it, it is just a query format structure and what you have there is a horrible mess.
I suggest that you move each CTE query out to separate queries and store the results of each one in temporary tables instead.
Also check your joins - you have declared outer joins with predicates in the WHERE clause that force them to be INNER joins, so just use inner joins for those.
You have a row_number partitioned by columns from an OUTER join table - did you really want to partition by NULLS?
I guess some mistakes may have been made when you were obfuscating the table/column names, and I hope the original query is less messy and confusing.
In summary, Divide'n'Conquer applies here.
By all means post back versions of the query as it develops if you need more pointers.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);