Bhuvnesh (3/15/2013)
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
I'm a bit surprised at that, Bhuvnesh. I thought you've been around long enough to know not to use such RBAR solutions. Try your solution on the following test data and let me know how long it takes. And, yeah... this is a real life test. I've been through such a situation before.
--Jeff Moden
Change is inevitable... Change for the better is not.