declare @max-2 int , @cnt int

declare @t table (id int ,Company varchar(50),Site nvarchar(20),TransDate Datetime)

set @cnt =1

--select * from #TransDetail

select @max-2 = MAX(Number) from #TransDetail

while ( @max-2 > = @cnt)

begin

insert into @t select @cnt, 'COMP','MyCompany','2012-12-01 00:00:00'

insert into @t select @cnt, 'COMPUSA','MyNewCompany','2012-12-02 00:00:00'

set @cnt = @cnt + 1

end

select

a.*

from #TransDetail b

right join @t a

on a.id = b.number and a.Company = b.Company

where b.number is null