Performance of a self join in a temporal table with a single day's difference between end_date and start_date
SELECT *
FROM self AS current 
LEFT OUTER JOIN self AS previous 
ON previous.employee_id = current.employee_id
AND DATEADD(d, 1, previous.end_date) = current.start_date

and previous.start_date &lt;&gt; current.start_date


or if has to be less  

and previous.start_date &lt;  current.start_date


or if can isolate on prior  

and previous.start_date = LAG (current.start_date, 1, 0) OVER (PARTITION BY current.employee_id ORDER BY current.start_date DESC)