declare @a table(id int,fnumber varchar(10),fname varchar(10)) insert into @a select 1,'1','aaa' union all select 2,'2','bbb' union all select 3,'3','ccc' union all select 4,'4','ddd' declare @b table(fnumber varchar(10),fcusnumber varchar(10),fre varchar(100),Fdate datetime) insert into @b select '1', '1','fdsfdsafds', '2009-05-01' union all select '2', '1','fdsf3232dsafds', '2009-05-02' union all select '3', '1','fdsfds222afds', '2009-05-03' union all select '4', '2','fdsfds111afds', '2009-05-15' union all select '5', '3','fdsfds222222afds', '2009-06-15' select * from @a select * from @b a where fdate<getdate()-30 and not exists( select 1 from @b where fcusnumber=a.fcusnumber and fdate>a.fdate)select * from @a a left join (select * from @b c where not exists(select 1 from @b where fcusnumber=c.fcusnumber and fdate>c.fdate) and fdate<getdate()-30 )as b on a.fnumber=b.fcusnumber
insert into @a select 1,'1','aaa' union all
select 2,'2','bbb' union all
select 3,'3','ccc' union all
select 4,'4','ddd'
declare @b table(fnumber varchar(10),fcusnumber varchar(10),fre varchar(100),Fdate datetime)
insert into @b select '1', '1','fdsfdsafds', '2009-05-01' union all
select '2', '1','fdsf3232dsafds', '2009-05-02' union all
select '3', '1','fdsfds222afds', '2009-05-03' union all
select '4', '2','fdsfds111afds', '2009-05-15' union all
select '5', '3','fdsfds222222afds', '2009-06-15' select * from @a
select * from @b a where fdate<getdate()-30 and not exists(
select 1 from @b where fcusnumber=a.fcusnumber and fdate>a.fdate)select * from @a a left join (select * from @b c where not exists(select 1 from @b where fcusnumber=c.fcusnumber
and fdate>c.fdate) and fdate<getdate()-30 )as b on a.fnumber=b.fcusnumber