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