create table #Account_Tbl (Account int, Owner int, Fee decimal(5, 2), [Account-Date] datetime)
insert #Account_Tbl values(11017 ,101 ,677.38 , 'Dec 31 1995')
insert #Account_Tbl values(11017 ,101 ,744.00 , 'May 30 1996 ')
insert #Account_Tbl values(11017 ,102 ,744.00 , 'Dec 31 1996 ')
insert #Account_Tbl values(11017 ,102 ,725.00 , 'Mar 31 1997 ')
insert #Account_Tbl values(11017 ,102 ,755.00 , 'Mar 31 1998 ')
insert #Account_Tbl values(11017 ,102 ,763.00 , null)


/*
Account Owner Fee Account-Date 

11017 101 744.00 May 30 1996 

11017 102 763.00 (null) 
*/
select a1.account, a1.owner, a1.fee, convert(varchar, a1.[Account-Date], 107)
from #Account_Tbl a1
where isnull(a1.[Account-Date], '9999-12-31 00:00') = 
                (select max(isnull(a2.[Account-Date], '9999-12-31 00:00')) from #Account_Tbl a2
                        where a1.account = a2.account and a1.owner = a2.owner)

This page was last updated on May 01, 2006 04:28 PM.