Ah, I see, test driven development:

;with cte as
(
    select * from (values ('Restaurant'),
           ...

Considering there's something urgent going on, why not tell us exactly what it is that you're trying to do and then we might be able to better help. All...

IF OBJECT_ID('tempdb..#temp','u') is not null
    DROP TABLE #temp
GO
SELECT *
  INTO #temp
  FROM (VALUES ('MN', 'KL'),
       ...

That's...

That may work but I need something a little different:

I need the if it is State = MN then Exclude ProdCodes IN ('Kl', 'Kjh')  Else if State not =...

Thanks for the reply, but that is not what I need.

I need a way to write this that when the state is MN then these ProcCodes are Excluded, but...

As a bit of a sidebar, you may have some people come back with a suggestion of using the built-in STRING_SPLIT() function.  That will seem to work but there...

Have you tried clearing the cache and running the queries again?

DBCC FREEPROCCACHE

You should look at partitioning the table by period.

Can you try this?

It should move the sort to the inserts into the clustered index on the temporary table

DROP TABLE IF EXISTS #temp;

CREATE TABLE #temp
(
  ...

The question is too long for me to read.

If you are inserting with DISTINCT, as you appear to be, that will ensure no duplicates are generated at the source

If you...

I would use an SSMS add-in like ApexSQL Refactor.

There is a free version of it here https://en.freedownloadmanager.org/Windows-PC/ApexSQL-Refactor-FREE.html

I think you only get a trial period with the current version from...

Alternatively using OUTER or CROSS APPLY

SELECT c.CityName, x.MonthName
  FROM dbo.City c
 OUTER APPLY (SELECT TOP(1) m.MonthName
          ...

Try this:

 FROM PROPERTY...

Use CROSS APPLY instead of INNER JOIN

  FROM PROPERTY P 
 INNER Join UNIT U   
    ON P.hMy = U.hProperty
 INNER...