>>Am I doing something out of the ordinary here? I thought this was a pretty basic query.
>>
>No you didn't. But both tables are not related, that way SQL Server must compare each record from Table1 with all records in Table2.
>
>BTW with LEFT JOIN you will get as many records as there are matching date intervals in Table2. Can you post some example data and desired result from it?

Sure:

Table1
ID  |Field1   |Field2   |EffectiveDate
1   |1.12     |2.51     |1/1/2007
2   |1.45     |2.56     |1/2/2007
3   |1.76     |2.81     |1/3/2007

Table2
ID  |Field1   |Field2   |StartDate   |EndDate
1   |9.12     |10.54    |11/1/2006   |1/2/2007
2   |9.01     |12.45    |1/3/2007    |1/31/2007

The results I want are:
1/1/2007   |1.12  |2.51   |9.12   |10.54
1/2/2007   |1.45  |2.56   |9.12   |10.54
1/3/2007   |1.76  |2.81   |9.01   |12.45