I'm not getting exactly the desired results you posted but maybe this will give you a hint.
;WITH CTE AS (
SELECT TOP 1 ID, TEXT1, aDate, TEXT2
FROM #Table1
JOIN #Table2 ON TEXT1 = TEXT2
UNION ALL
SELECT a.ID, a.TEXT1, b.aDate, b.TEXT2
FROM #Table1 a
JOIN CTE c ON a.ID = c.ID + 1
JOIN #Table2 b ON a.TEXT1 = b.TEXT2 AND b.aDate >= c.aDate
)
SELECT TEXT1, aDate, TEXT2
FROM (
SELECT TEXT1, aDate, TEXT2
,n=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY aDate)
FROM CTE) a
WHERE n = 1
OPTION (MAXRECURSION 0)
Where the code says AND b.aDate >= c.aDate, try making that AND b.aDate > c.aDate and run it both ways. I think this is close and that the answer may lie in reinterpreting the expected results from the sample data.
Unfortunately this is probably going to be quite slow depending on the number of rows in your actual data.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St