Recursive Matching using CTE Query in SQL Server

The CTE usage was a bit strange to me, since you're not really doing much with it in the subsequent query. I'd move that to the UPDATE.

The query doesn't really join on the Source. I'm not sure if it's meant to do that or not. If UTab has multiple sources for MRN/LSPEC combination, that might result in an issue.

So, I come up with something like:

DECLARE @ADT_Adjustment INT = 0;DECLARE @SDT_Adjustment INT = 1;DECLARE @Iteration INT = 1;WITH SequencedJoin AS (    SELECT        ETab.MRN, ETab.LSPEC, ETab.ADT, ETab.SDT, UTab.SIDate, ETab.Enum, ETab.[Source], UTab.Enum AS WriteEnum, UTab.Iteration AS WriteIteration    ,   DENSE_RANK() OVER (             PARTITION BY UTab.MRN, UTab.LSPEC, UTab.[Source], UTab.SIDate             ORDER BY ABS( DATEDIFF( MINUTE, UTab.SIDate, ETab.ADT ) )         ) AS Ordinal    FROM        @UTab AS UTab    JOIN    @ETab AS ETab ON (            ETab.MRN = UTab.MRN        AND ETab.LSPEC = UTab.LSPEC        AND ETab.[Source] = UTab.[Source]        AND UTab.SIDate BETWEEN DATEADD( dd, -@ADT_Adjustment, ETab.ADT ) AND DATEADD( dd, @SDT_Adjustment, ETab.SDT )        )    WHERE        UTab.Iteration IS NULL)UPDATE    SequencedJoinSET    WriteEnum = SequencedJoin.Enum,   WriteIteration = N'Iteration#' + CAST( @Iteration AS VARCHAR( 2 ) )WHERE    SequencedJoin.[Source] = 'OP'AND SequencedJoin.Ordinal = 1

up.L != cte.L since you are looking for anything conforming your conditions and filtering by rn.

SELECT cte.E, [Iteration] = N'00-00-00-CA', *FROM [Up]     INNER JOIN cte ON [Up].[M] = [cte].[M] AND [cte].[rn] = 1WHERE [cte].[E] IS NOT NULL AND (    [Up].[DTE] BETWEEN         DATEADD(dd, -0, [cte].[ADT]) AND         DATEADD(dd, 0, [cte].[SDT]))             AND [Up].[F] = 'Y'             AND [Up].[S] = 'HC'             ----comment this line            --AND [Up].[L] = [cte].[L]; -- <<<<<<<<<<<<<<<<

http://sqlfiddle.com/#!18/d1483/2/0

I modified some data: added ZZ which broke your query totally. Only two rows became matching.

ps fixed insert issue with length of E column and col name listed in first insert.

Not a complete answer as such, but this index will speed up you CTE:

CREATE INDEX T1 ON UTAB (    MRN,    SIDATE)INCLUDE(    LSPEC,    Iteration)