I'm not clear on what you're asking for help on. I see the code, I see you want to do something in a Join statement instead of "at the end" (do you mean in a Where clause?), but it's not clear to me what precisely you are asking.

As an aside, you can simplify your drop-checks on the temp tables. Instead of "if exists ....", just "if object_id(N'tempdb..#MyTempTable') is not null" works just fine, and takes less steps. Microscopically faster, since you're already checking the object_id function in your Where clause, too.