I am just wondering if simplest is the best option here...

Get the top record (sorted in date descending order before the query time) for the accountID

get its active status

if there is no record (NULL) then it cant have been activated so set activeflag = 0

create function dbo.isaccountActive(@accountID int, @comparisonTime datetime)

returns int

as

begin

declare @activeFlag int

select top 1

@activeFlag = active

from

account_DeactivationReactivationHistory

where

account_ID = @accountID and

statusDate <= @comparisonTime

order by statusDate desc

set @activeFlag = isnull(@activeFlag,0)

return @activeFlag

end