from TBadmUser#

where UserId = @userid

and AuditStatus <> 'D'

and A.AuditDate = (select max(AuditDate) from TBadmUser#

where UserId = A.UserId

and AuditDate <= @AsOf)

and @AsOf is not null

);

Several problems. 1, what is @AsOf is a future date? Your function returns nothing from live or archive table. 2, the select max audit date subquery does not specify "and AuditStatus <> 'D'", so it can return a date that will be excluded by the select statement. The first one is hypothetical, but the second one is a failure type error.

3, why select the max(AuditDate) in the subquery instead of selecting the primary key of the archive table, that seems like a better choice since then the top level select is selecting where PK = known value.