Statment running significantly slower than component parts and causes high CPU usage
You should check your execution plan.

It can be that cardinality estimation for the last Anti Semi Join is wrong: 
you join 247.769 rows with 237.660 rows and there should be Hash Join but if it underestimated cardinality (AdminWIP_DS.dbo.fnTblMembers()) it used NL so you've got your 20 minutes.

Another thing to consider here is to eliminate right join:

You concatenate 2 strings and if one of them is null you'll get null as the result.