Something like this:

IF EXISTS(SELECT * FROM #temp1)
AND EXISTS(SELECT * FROM #temp2)
  SELECT * FROM #temp1
  INTERSECT
  SELECT * FROM #temp2;
ELSE
 ...

I wrote a script some time ago and shared it in this site. http://www.sqlservercentral.com/scripts/constraints/133573/
It's not perfect, but works with most options. It works for Primary Keys, Foreign...

Just remember that those queries will always scan the whole table....

You can do something like this:

SELECT CAST( created_time AS date) AS "created time",
  tlorder.origcity AS "Destination",
  COUNT(*) AS "Number ofOrders",
  SUM(total_charges)...

Option 1: Tune the query (recommended). This is done on SQL Server code.
Option 2: Increase the time before the connection times out. This is done in .NET code.

If we're already using non-SARGable expressions, then let's do it for all the conditions.

Select *
From ##Activity_Tracking
WHERE Referral_C + Note_Ordered_C + Breach_Ordered_C + Breach_Recvd_C...

Here's an example on how to do it. Try to complete it with the missing columns.
Ask any questions you might have.
SELECT past.past_15
  ,past.past_30
  ,past.past_60

If the seed is the only that would vary, you can avoid the dynamic sql.

IF (SELECT OBJECT_ID('Tempdb..#tmp3')) IS NOT NULL
DROP TABLE #tmp3;

DECLARE...

I forgot to mention something.
If you really need the PersonID to be unique, then add a UNIQUE constraint to that column

Why would you only have one note for each person? That seems illogical.
However, here's an alternative that should handle concurrency in a better way.

BEGIN...