Not sure if I read the statement correctly but it seems that you don't use
@CardNo
anywhere. You only use
@Time
so the statement would fetch all the rows from
tblPersonnelDetails
, actually multiple times, as many times as row is found for
@Data
depending on
EffectiveDate
.
So if you want to fetch the rows based on max of Time column, perhaps just
SELECT CASE
WHEN details.[EffectiveDate] = currentDetails.[CurrentEffectiveDate] THEN 1
ELSE 0
END AS IsCurrent,
details.PersonnelBASeId,
details.EffectiveDate,
details.CardNo
INTO ##tmp
FROM tblPersonnelDetails AS details
LEFT OUTER JOIN
( SELECT PersonnelBASeId,
MAX(EffectiveDate) AS CurrentEffectiveDate
FROM tblPersonnelDetails
WHERE (ISNULL(Deleted, 0) = 0)
AND (EffectiveDate <= (SELECT MAX([Time]) FROM @Data))
GROUP BY PersonnelBASeId) AS currentDetails
ON details.PersonnelBASeId = currentDetails.PersonnelBASeId
WHERE (ISNULL(details.Deleted, 0) = 0)
ORDER BY EffectiveDate DESC,PersonnelBaseId DESC
But as said that feels a bit funny. If you intend to fetch only the times for a specific cardno, just add the correlation to the inner query.