CREATE TABLE #Table1 (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	TEXT1 varchar(100) );
	
CREATE TABLE #Table2 (
	aDate DATETIME NOT NULL,
	TEXT2 varchar(100) );
CREATE CLUSTERED INDEX IX_aDate ON #Table2(aDate);	

INSERT INTO #Table1 (TEXT1)
SELECT 'AAAA' UNION ALL
SELECT 'BBBB' UNION ALL		
SELECT 'AAAA' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'CCCC' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'AAAA' UNION ALL
SELECT 'CCCC' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'CCCC' ;

SELECT * FROM #Table1;

INSERT INTO #Table2 (aDate, TEXT2)
SELECT '2012-01-01 12:00:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:01:00', 'AAAA' UNION ALL
SELECT '2012-01-01 12:02:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:02:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:02:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:03:00', 'AAAA' UNION ALL
SELECT '2012-01-01 12:03:00', 'AAAA' UNION ALL
SELECT '2012-01-01 12:04:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:05:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:06:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:07:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:08:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:08:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:08:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:10:00', 'AAAA' UNION ALL
SELECT '2012-01-01 12:11:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:12:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:12:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:14:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:14:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:14:00', 'BBBB';

-- Desired Result
ID  Text1	              aDate		        Text2
1	'AAAA',	'2012-01-01 12:01:00', 'AAAA'
2	'BBBB',	'2012-01-01 12:02:00', 'BBBB'
3	'AAAA',	'2012-01-01 12:03:00', 'AAAA'
4	'BBBB',	'2012-01-01 12:04:00', 'BBBB'
5	'CCCC',	'2012-01-01 12:06:00', 'CCCC'
6	'BBBB',	'2012-01-01 12:07:00', 'BBBB'
7	'BBBB',	'2012-01-01 12:08:00', 'BBBB'
8	'AAAA',	'2012-01-01 12:10:00', 'AAAA'
9	'CCCC',	'2012-01-01 12:12:00', 'CCCC' 
10	'BBBB',	'2012-01-01 12:12:00', 'BBBB'
11	'CCCC',	'2012-01-01 12:14:00', 'CCCC' 

-- First Add a ROW_Number to Table2 
ALTER TABLE #Table2
	ADD RowNum INT;
WITH SetRows AS (
	SELECT ROW_NUMBER() OVER(ORDER BY aDate, TEXT2) As RN, aDate, Text2
	FROM #Table2 )
UPDATE T2 
	SET RowNum 	= RN
FROM SetRows AS S 
JOIN #Table2 AS T2 ON S.aDate = T2.aDate AND S.TEXT2 = T2.TEXT2	

-- Gets too many rows but does include the required results
;WITH Numbered AS (
	SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Row1, ID, TEXT1	
	FROM #Table1 
), Numbered2 AS (
	SELECT ROW_NUMBER() OVER(ORDER BY ID, aDate) AS RNMatch, 
		ROW_NUMBER() OVER( PARTITION BY aDate ORDER BY ID, aDate)  AS PartNo, Row1, ID, TEXT1, T2.RowNum, T2.aDate, T2.TEXT2
	FROM Numbered 	AS T1 
	JOIN #Table2 AS T2 ON T1.TEXT1 = T2.TEXT2	
	AND T2.RowNum >= Row1
)
SELECT N1.PartNo, N1.Row1, N1.ID, N1.TEXT1, N1.RowNum, N1.aDate, N1.TEXT2 
FROM Numbered2 AS N1
JOIN Numbered2 AS N2 ON N1.RNMatch = N2.RNMatch AND  N2.PartNo >= N1.PartNo
ORDER BY N1.ID, N2.aDate 

SELECT *, ROW_NUMBER() over (partition by Text1 order by Text1) as RowNum FROM #Table1 order by TEXT1
select *, ROW_NUMBER() over (partition by Text2 order by aDate, Text2) as RowNum from #Table2 order by TEXT2

ALTER TABLE #Table2
	ADD RowNum INT;
WITH SetRows AS (
	SELECT ROW_NUMBER() OVER(ORDER BY aDate) As RN, aDate, Text2
	FROM #Table2 )
UPDATE T2 
	SET RowNum 	= RN
FROM SetRows AS S 
JOIN #Table2 AS T2 ON S.aDate = T2.aDate AND S.TEXT2 = T2.TEXT2	



--- Sod it - use a cursor
DECLARE @ROW1 INT, @ID INT, @TEXT1 VARCHAR(100);
DECLARE @MatchDate DATETIME, @TEXT2 VARCHAR(100);
DECLARE @MatchPos INT;
DECLARE @RESULTS TABLE (ID INT PRIMARY KEY, TEXT1 varchar(100), aDate DATETIME, TEXT2 varchar(100), aDate2 DATETIME );
INSERT INTO @RESULTS (ID, TEXT1)
SELECT ID, TEXT1 
FROM #Table1;
DECLARE  SODIT CURSOR FAST_FORWARD FOR
	SELECT ROW_NUMBER() OVER(ORDER BY T1.ID) AS Row1, T1.ID, T1.TEXT1
	FROM #Table1 AS T1
	ORDER BY T1.ID

OPEN  SODIT;
SET @MatchDate = '2000-01-01'; -- just a minimum date
SET @MatchPos = -1;
FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
WHILE(@@FETCH_STATUS = 0)
BEGIN

	SELECT @MatchPos = MIN(T2.RowNum)
	FROM #Table2 AS T2
	WHERE T2.TEXT2 = @TEXT1
	AND T2.RowNum > @MatchPos;
	
	SELECT @MatchDate = T2.aDate
	FROM #Table2 AS T2
	WHERE T2.RowNum = @MatchPos;

	UPDATE @RESULTS			
		SET TEXT1= @TEXT1, 
			aDate = @MatchDate
	WHERE ID = @ID;
	
	FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
END

CLOSE  SODIT;
DEALLOCATE  SODIT;

DECLARE  SODIT CURSOR FAST_FORWARD FOR
	SELECT ROW_NUMBER() OVER(ORDER BY T1.ID) AS Row1, T1.ID, T1.TEXT1
	FROM #Table1 AS T1
	ORDER BY T1.ID

OPEN  SODIT;
SET @MatchDate = '2000-01-01'; -- just a minimum date
SET @MatchPos = -1;
FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
WHILE(@@FETCH_STATUS = 0)
BEGIN
	SELECT @MatchDate = MIN(T2.aDate) 
	FROM #Table2 AS T2
	WHERE T2.TEXT2 = @TEXT1
	AND T2.aDate >= @MatchDate
		
	UPDATE @RESULTS	
		SET TEXT2 = @TEXT1,
		aDate2 = @MatchDate
	WHERE ID = @ID;
	
	FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
END

CLOSE  SODIT;
DEALLOCATE  SODIT;
SELECT * FROM @RESULTS ORDER BY ID;

;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)