I think you need to force the join order with something like:

select T1.[Name], T1.tracking_number, T2.start_date, T3.first_name, T3.last_name

from Table1 T1

 left join

 (

  Table2 T2

  join

  ( select T22.tracking_number, max(T22.start_date) as start_date

   from Table2 T22

   group by T22.tracking_number) D

  on...

I think this would be best done either in the middle tier or by using a cursor.

If you have less than 1000 rows something like the following ghastly looking query...

Try sp_executesql with output parameters. Something like:

declare @EmployeeNo int, @AccessLevelID int, @err int

set @queryString = 'select @EmployeeNo = b.employee_no, @AccessLevelID = a.Access_Seq from '

  + @dbName + '..employee a, '...

-- Test data

declare @t table

(

 refNo char(5) not null primary key

)

insert @t

select 'ABC-0' union all

select 'ABC-1' union all

select 'XYZ-0' union all

select 'XYZ-1' union all

select 'XYZ-2' union all

select 'PQR-0' union all

select 'MNO-0'...

Maybe you are trying to do something like:

DECLARE @t TABLE

-- or create a temp table

(

 Policy_ProductId int not null primary key

 ,RowNum int identity not null )

INSERT @t (Policy_ProductId)

SELECT Policy_ProductId

FROM Policy_Product

INSERT INTO...

David,

WOW!

It took me 15 minutes to understand what you did. It is a lot more efficient than my approach.

All methods with their percentage of batch cost are below:

declare @t table

(

 tDate...

Take the query:

select T.tDate as DateFrom

 ,isnull

 (

  (select min(tdate)

  from @t T1

  where T1.price <> T.price

   and T1.tdate > T.tdate) - 1

 , D.MaxTDate) as DateTo

 ,T.price

from @t T cross join

 (

  select min(T5.tdate),...

The select statements are only test data. You will have to adapt the query so it works on your table.

This is more efficient:

select T.tDate as DateFrom

 ,isnull

 (

  (select min(tdate)

  from @t T1

  where T1.price <> T.price

   and T1.tdate > T.tdate) - 1

 , D.MaxTDate) as DateTo

 ,T.price

from @t T cross join

 (

  select min(T5.tdate),...

Try something like:

select *

from dbo.Companies C

 join dbo.Contacts E on C.companyId = E.companyId

where E.contactId = ( select min(E1.contactId)

 from dbo.Contacts E1

 where E1.companyID = C.companyID)

There is probably a better way, but this seems to work.

declare @t table

(

 tDate datetime not null primary key

 ,price int not null

)

insert @t

select '20070101', 100 union all

select '20070102', 100 union all

select...

try:

 INSERT INTO my_table (comments,

                                sr_status,

                                sr_notes_count,

                                sr_activity_count,

                                last_updated_dt)

 SELECT 'some input comments',

             (SELECT max(status) FROM my_table_2 WHERE id = 123),

             (SELECT count(*) FROM my_table_3 WHERE id = 123),

             (SELECT count(*) FROM...

PW's approach is more efficient.

--test data

declare @t table

(

 tName varchar(20) not null

 ,Event varchar(20) not null

 ,tTime datetime not null

)

-- 7.34% of cost

insert @t

select 'Name1', 'Start', convert(datetime, '8/16/06 8:00 AM', 0) union all

select...

--test data

declare @t table

(

 tName varchar(20) not null

 ,Event varchar(20) not null

 ,tTime datetime not null)

insert @t

select 'Name1', 'Start', convert(datetime, '8/16/06 8:00 AM', 0) union all

select 'Name2', 'Start', convert(datetime, '8/16/06 8:15 AM', 0)...

Run the profiler on the bits of the application which do not work and see what is called just before they fail.