Try this:
select AccountID, AccountType, AccountValue, ValueDate,
(select SupervisedStatus
from
(select AccountID, SupervisedStatus, FromDate,
isnull((select min(fromdate)
from #SupervisedStatus s2
where fromdate > s1.fromdate
and accountid = s1.accountid), getdate()) as ToDate
from #SupervisedStatus s1) Sub1
where AccountID = #Account.AccountID
and FromDate <= #Account.ValueDate
and ToDate > #Account.ValueDate)
from #Account;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon