Such a question is:

1.) IMPROPER, as it violates the basic separation of question from answer.  Such questions can NOT be properly answered without invalidating the question.
2.) IMPROPER...

I'm not sure you need a For Each loop container, because the default assumption is that your recordset get's processed in a manner that treats each row to all the...

I'm not sure I understand why a bunch of dates in the relatively distant past are in any way relevant to what you are doing.   Also, would you really want...

Also, the server versions may well matter, as executing code with T-SQL feature such as LEAD or LAG on a server prior to version 2012 won't work.

I'm assuming you are executing these from the local server.   That means that even though you are executing a remote stored procedure in the first case, you are executing that...

Well, your error is accurate.   A Job has to be "owned" by SA in order to be able to write to the log.  There's no way around that restriction that...

I'm still not clear on why there's the desired output lists data that is not present nor easily determined from the existing data, due to unequal numbers of elements in...

CASE
    WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
    WHEN LEN(B.LAB_LOC_CD) = 2 AND B.WH_STATE_CD = 'NA' THEN ' '
    WHEN B.WH_STATE_CD = 'NA' THEN ' '
    WHEN...

Try this on for size:SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @StartDt AS date = '01/01/2016', @EndDt AS date = '09/29/2017';

IF OBJECT_ID('tempdb..#AllCases', N'U') IS...