Excellent job posting ddl and sample data!!! I am a bit confused on what you are trying to do here though. I know you posted your desired output (another huge kudos!!!). However I can't figure out the logic here at all.

If we look just at AAA you have 3 rows in your results but I can't figure out what the logic of the value of the date is.

This query is what I was using to try to understand what you are looking for but it just doesn't quite match up.

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

For AAA RowNum 1 = RowNum 1 from table2, so far so good.

AAA RowNum 2 = RowNum 2 from table2 ...still makes sense

AAA RowNum 3 = RowNum 4 from table2 ??? Why is this????

Then when you get to BBB the first one starts with row 2 from table 2 and then doesn't make any sense at all to me. You are getting rows 2,4,5 from table 2.

I suspect there must be some sort of reasoning here but I can't figure it out.