wbrianwhite (11/4/2010)
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.
You could also find the archive row with just a left outer self join instead of a correlated subquery. Something like this (haven't tested it):
from TBadmUser# A with(nolock)
left join TBadmUser# B with(nolock)
on A.UserId = B.UserId
and A.UserId = @userid
and A.AuditStatus <> 'D'
and B.AuditStatus <> 'D'
and B.AuditDate < A.AuditDate
where A.AuditDate <= @AsOf
and B.UserId is null
I've seen left outer self joins perform much better than correlated subqueries that use aggregated functions. Though they're a bit harder to read at first, once you're familiar with the pattern they're fine.