it looks like the problem isn't with your joins, but that you don't seem to compare the value of submmitedon in each row to the maximum value in your date...

Do you have a proxy account setup for XP_CMDSHELL?
https://msdn.microsoft.com/en-us/library/ms190359.aspx
This defines what windows account that XP_CMDSHELL will run as, so you can ensure it's an account that has...

Do you have the query plan from this query that you can post, as a .sqlplan file?  Also, what indexes are on these 3 tables?  The OUTER APPLY should work...

maybe it could be simplified like this instead of physically counting all the records?

SELECT 1 WHERE(
EXISTS (SELECT NULL FROM View1 WHERE field1 = 'Y')
OR

it is normal for SQL Server to use as much memory as it can, since it wants to cache things like your data, indexes, query execution plans, etc in memory...

Is the OrderID column in the Orders table an IDENTITY column?  Either way if it is or not I'd imagine that it would not be IDENTITY in the orders_arch table,...

Probably the only thing I'd change would be the order of a couple of things to make sure dependencies between those items are met.  For example, in my environment there...