May be you should try to take DAY of date from Table1 and see if it is in the date range?

The way it's currently written you have it too complex and it may result in cartesian product.

That's just a general idea, I bet this kind of problem was already discussed, I made a quick search here, but didn't find anything too relevant.

>I am trying to fine tune a SPROC that is taking about 25 seconds to run.
>
>I have one table with a record every day. I have a second table with a record for a time frame, and it has a StartDate and EndDate field.
>
>I want to link the tables together in one query. I have been doing something like this:
>
>

>SELECT
>     Table1.Table1ID
>     , Table1.Field1
>     , Table1.Field2
>     , Table1.EffectiveDate
>     , Table2.Table2ID
>     , Table2.Field1
>FROM
>     Table1
>          LEFT OUTER JOIN Table2 ON (Table1.EffectiveDate > Table2.StartDate AND Table1.EffectiveDate < Table2.EndDate)
>

>
>This is getting me the results I want, but at a performance price. Is there a better way of doing this, like loading Table2 into a temp table before joining? Thanks!