You could use a single query like below. I have not tested it against a 4.5 million row input set, but it's probably faster then the loop and all the separate queries you are using right now. I recommend to include the column LinkRL4 to the non-clustered index on ProcessDate. If the zero-offset row numbering in case of a previous row outside the data range is not important, remove the CASE expression.

SELECT

evt.*,

RowNumber = ROW_NUMBER() OVER (PARTITION BY evt.LinkRL4 ORDER BY evt.L4Ident) -

CASE WHEN MIN(evt.ProcessDate) OVER (PARTITION BY evt.LinkRL4) < @StartDate THEN 1 ELSE 0 END

FROM

dbo.#Events evt

WHERE

evt.ProcessDate <= @EndDate

AND NOT EXISTS

(

SELECT

*

FROM

dbo.#Events evt2

WHERE

evt2.LinkRL4 = evt.LinkRL4

AND evt2.ProcessDate > evt.ProcessDate

AND evt2.ProcessDate < @StartDate

)

ORDER BY

evt.LinkRL4, evt.ProcessDate, evt.L4Ident