dwain.c (11/15/2012)

---

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.