I've never seen a LEFT JOIN written as a CTE, used in another CTE before.
With the 2nd CTE reading like a recursive one (anchor stmt, union all, recursive stmt) with NOLOCK **AND** index hint.
1. Don't do that
2. If you call the 1st CTE in both parts of the recursive CTE, then its not recursive!!
about 1 month ago