<pre>CREATE PROCEDURE [dbo].[PersonnelDetails_GetPersonnelByCardNoAndTime] ( @CardNo VARCHAR(16), @Time SMALLDATETIME ) AS DECLARE @PersonnelBaseID INT; DECLARE @EffectiveDate SMALLDATETIME; SELECT EffectiveDate,PersonnelBaseID,CardNo INTO #PersonnelDetails FROM ( SELECT personnelDetails.EffectiveDate, personnelDetails.PersonnelBaseID, CardNo FROM dbo.tkp_PersonnelDetails AS personnelDetails INNER JOIN ( 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 )result ON (result.PersonnelBaseID = personnelDetails.PersonnelBaseID AND result.EffectiveDate = personnelDetails.EffectiveDate AND personnelDetails.Deleted = 0 AND CardNo = @CardNo) )details WHERE CardNo = @CardNo ORDER BY details.Effectivedate DESC SELECT TOP 1 @PersonnelBaseID = PersonnelBaseID , @EffectiveDate = EffectiveDate, @CardNo = CardNo FROM #PersonnelDetails ORDER BY Effectivedate DESC IF ((SELECT COUNT(DISTINCT PersonnelBaseID) FROM #PersonnelDetails WHERE EffectiveDate = @EffectiveDate AND CardNo = @CardNo) > 1)--handle Multi Personnel With On CardNo In same EffectiveDate :( SELECT 0; ELSE SELECT @PersonnelBaseID; DROP TABLE #PersonnelDetails
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)