select * from tablename a where not exists ( select 1 from tablename where p=a.p and date>a.date and date<getdate() ) and date<getdate()
select * from tablename a where date<getdate() and not exists ( select 1 from tablename where p=a.p and date>a.date )
create table testT(p char,n dec,date datetime) insert testT select 'A',1,'2006-1-1' union all select 'B',2,'2006-3-6' union all select 'A',1.5,'2006-5-1' union all select 'A',1.6,'2006-8-1' union all select 'A',2,'2006-12-15' union all select 'B',2.1,'2006-12-1' select p,max(date) from testT where date<getdate() group by pdrop table testT
where not exists (
select 1 from tablename
where p=a.p
and date>a.date
and date<getdate()
)
and date<getdate()
where date<getdate()
and not exists (
select 1 from tablename
where p=a.p
and date>a.date
)
insert testT
select 'A',1,'2006-1-1' union all
select 'B',2,'2006-3-6' union all
select 'A',1.5,'2006-5-1' union all
select 'A',1.6,'2006-8-1' union all
select 'A',2,'2006-12-15' union all
select 'B',2.1,'2006-12-1'
select p,max(date)
from testT
where date<getdate()
group by pdrop table testT