Hi,
Please refer below script
SQL
DECLARE @Applicant AS TABLE(ApplicantID INT,ApplicantName VARCHAR(100),DateApplied DATETIME,Approver VARCHAR(100),ApprovedDate DATETIME,StatusId INT)
INSERT INTO @Applicant VALUES
(1,'David','2015-02-02','Andrea','2015-02-02',3),
(2,'Kevin','2015-02-02','Andrea','2015-02-02',3),
(3,'Jhon','2015-02-02','Alley','2015-02-02',4),
(4,'Peter','2015-02-02','Laxman','2015-02-02',2)
DECLARE @Status AS TABLE(StatusID INT,StatusName VARCHAR(100),Lapsed CHAR(1))
INSERT INTO @Status VALUES
(0,'Not-Approved','N'),
(1,'Approved By Verifier','N'),
(2,'Approved By Doctor','N'),
(3,'Approved By Senior Doctor','N'),
(4,'Approved By Manager','N')
DECLARE @ApproverLevels AS TABLE(LevelId INT,LevelName VARCHAR(100))
INSERT INTO @ApproverLevels VALUES
(11,'Varifier'),
(12,'Doctor'),
(13,'Sr Doctor'),
(14,'Manager')
DECLARE @Approver AS TABLE(ApproverId INT,ApproverName VARCHAR(100),ApproverLevelId INT)
INSERT INTO @Approver VALUES
(1,'Alley',11),
(2,'Andrea',12),
(3,'Laxman',13),
(4,'Manoj',14)
DECLARE @MappingTableForApprover AS TABLE(MappingId INT,ApproverLevelId INT,Lapsed CHAR(1))
INSERT INTO @MappingTableForApprover VALUES
(1,11,'N'),
(2,12,'N'),
(3,13,'N'),
(4,14,'N')
DECLARE @MappingTableForStatus AS TABLE(Id INT,MappingId INT,StatusId INT,Lapsed CHAR(1))
INSERT INTO @MappingTableForStatus VALUES
(1,1,4,'N'),
(2,2,3,'N'),
(3,3,2,'N'),
(4,4,1,'N')
DECLARE @ApproverId INT
SET @ApproverId = 4
SELECT DISTINCT aplnt.*
FROM @Applicant aplnt,
@MappingTableForStatus mts,
@MappingTableForApprover mtpr,
@Approver apr,
@ApproverLevels aprl,
@Status st
WHERE aplnt.StatusId = mts.StatusId
AND mtpr.MappingId = mts.MappingId
AND apr.ApproverLevelId = mtpr.ApproverLevelId
AND apr.ApproverLevelId = aprl.LevelId
AND apr.ApproverId = @ApproverId
AND mts.StatusId = aplnt.StatusId
AND st.StatusID = aplnt.StatusId
AND mts.Lapsed <> 'Y'
AND mtpr.Lapsed <> 'Y'
Screenshot
