You would want a nonclustered index on dbo.OrderProducts ( OrderID, ProductID ) to support this query.

--using variables to make the code easier to use and maintain
--may specify...

SELECT ca1.*
FROM TableA a
CROSS APPLY (
    SELECT A.Extension, A.Name, A.UsageCount, 'TableB' AS [Used By], B.VDN AS [Usage Data]
    FROM TableB B
...

Hmm.  I'm not sure how we are supposed to debug this not seeing any of the SQL used to create the table.

Can you at least post the DDL that you...

...

SELECT SUBSTRING(V.YourString,CI4.CI+1, CI5.CI - CI4.CI - 1)
FROM (VALUES('Data:A:B:C:20:0:0'))V(YourString)
     CROSS APPLY (VALUES(CHARINDEX(':',V.YourString)))CI1(CI)
     CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI1.CI+1)))CI2(CI)
    ...

SELECT 
    COALESCE(ABEE.Operator_Ref, ABG.Operator_Ref, GMBH.Operator_Ref) AS Operator_Ref, 
    CA1.[DateTime_Started]

FROM (
    SELECT Operator_Ref, 
     ...

I still don't get your insistence on the guid being so always great as the clustering key for a table.

As a stand-alone table, maybe, IF all the rows...

I still don't get your insistence on the guid being so always great as the clustering key for a table.

As a stand-alone table, maybe, IF all the rows inserted were...

That's relatively easy to do:

DECLARE @table1 sysname
DECLARE @table2 sysname

SET @table1 = 'dbo.table1' --<<--!!CHANGE TABLES NAMES HERE!!
SET @table2 = 'dbo.table2' --<<--!!CHANGE TABLES NAMES HERE!!

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
...

If no matching PATINDEX string was found in the data, then it would default to the start of the string, which might seem to be weird results if you didn't...

Btw:

Are you using compressed backups?

Do you use page compression on the data in large tables?

If a job is currently running, an attempt to start the job will fail (it will essentially be ignored).

Say JobA starts at 1PM and is schedule to start very hour. ...

You've provided us NO sample data, as Jeff mentioned.  We can't know what your data is unless you tell us.  Nor does "whacky results" really tell us anything.

Good luck with...

Rather tricky, but this works with the one example you posted.  I believe it will work with others, but you'd need to post more sample data for me to test...

If the day is < 10, is it always padded?  That is, '3/05/2021' vs '3/5/2021'?  Based on your code, it looks like you allow the month to be a single...

Hmm, I think it's a waste to...