Taken that the logic should not change, you can make different kinds of modifications of the statement but you cannot completely remove the join since that would affect the results.
For example one variation could be
WITH r1 (
SELECT Max(EffectiveDate) AS EffectiveDate,details.PersonnelBaseID
FROM dbo.tkp_PersonnelDetails AS details INNER JOIN
prs_Personnel personnel ON personnel.ID = details.PersonnelBaseID AND
Personnel.Active = 1 AND
Personnel.Deleted = 0 AND
details.Deleted = 0 AND
details.EffectiveDate <= @Time
GROUP BY PersonnelBaseID
)
SELECT Top 1 details.PersonnelBaseID FROM dbo.tkp_PersonnelDetails INNER JOIN
(
SELECT personnelDetails.EffectiveDate,personnelDetails.PersonnelBaseID,CardNo
FROM dbo.tkp_PersonnelDetails AS personnelDetails
INNER JOIN r1 ON r1.PersonnelBaseID = personnelDetails.PersonnelBaseId
AND r1.EffectiveDate = personnelDetails.EffectiveDate
) details ON tkp_PersonnelDetails.PersonnelBaseID = details.PersonnelBaseId
AND tkp_PersonnelDetails.cardNo = @CardNo
ORDER BY tkp_PersonnelDetails.Effectivedate DESC
Another variation could be to use an EXISTS structure.
But what comes to the performance, the first thing to do is to check if you have all necessary indexes in place. For example something like:
- Table prs_Personnel columns Id, Active, Deleted
- Table tkp_PersonnelDetails columns PersonnelBaseID, Deleted, EffectiveDate