CAST the column formula to the data type you want it to be, e.g.:

,CAST(Concat(Convert(varchar(10),Date,23) , ' ',REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar(8),time,100),7)),7),'AM',' AM'),'PM',' PM')) AS datetime) as DateTime

Maybe this instead:

SELECT

DENSE_RANK() OVER (ORDER BY QuestionCategoryID) CategoryNumber,

...

SELECT whatever
FROM dbo.table_name
WHERE date_column BETWEEN CAST(YEAR(GETDATE()) - 1 AS varchar(4)) + '07' AND
    CAST(YEAR(GETDATE() AS varchar(4)) + '06'

If you need absolutely sequential numbers, no gaps, then you could use ROW_NUMBER() to generate a value to be added to the MAX() value determined before the INSERTs.

DECLARE @max_ID int

SELECT...

Select custnbr 
from #plist
group by custnbr
having sum(case when prlist like 'pli%' then 1 else 0 end) = 1 and
    sum(case when prlist like '[ac]b%'...

Do you mean you can't use a SEQUENCE?  I can't imagine why.  I use those all the time when for whatever reason an identity is not appropriate.  And there's a...

First I would think would be to try this:

GRANT SELECT ON dbo.view_name TO [domain\account];

GRANT SELECT ON dbo.view_name2 ...

Use a table to store the date ranges, then join to that.

DROP TABLE IF EXISTS #DATES;
CREATE TABLE #DATES (
    VARIABLEA date NOT NULL,
 ...

You're also going to need to do something about the:

ar.Account_B__c as Account_ID,

a.SalesOrg__c as SalesOrg,

columns.  They are not in the GROUP BY and they are not within an aggregate function, so...

Best would be not to shrink the main db at all.  If possible, add a secondary filegroup, create the temp tables in there, then shrink only the file(s) in that...

You're good there, so that's not part of the issue.

Are you on the latest CU(patch-level)?

Have you verified that the threshold for parallelism setting is not too low?

Btw, neither "matched" nor "user_id" are SQL Server reserved keywords.

A trigger makes the most sense to me, so that the assignments are immediate.  You would also need a DELETE trigger to remove interested / mutual for a deleted user_id...

Btw, as noted by some others, I too, fortunately, have never seen any automated tool that can really properly analyze index usage and make really good suggestions for changes.  You...