SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.EMPLOYEE_APPLICATION (
	EMPLOYEE_APPLICATION_ID bigint NOT NULL,
	EXPECTED_DESIGNATION_ID bigint NULL,
    EXPECTED_DESIGNATION_NAME varchar(255) NULL,
	EMPLOYEE_ID bigint NULL
);

INSERT dbo.EMPLOYEE_APPLICATION VALUES
(1, 2, 'Lecturer(ICT)', 100),
(2, 4, 'Lecturer(ENG)', 200)

CREATE TABLE dbo.EMPLOYEE_EDUCATIONAL_QUALIFICATION (
	EMPLOYEE_EDUCATIONAL_QUALIFICATION_ID bigint NOT NULL,
	EDUCATION_DEGREE_ID bigint NULL,
    EDUCATION_DEGREE_NAME varchar(255) NULL,
	SUBJECT_ID bigint NULL,
    SUBJECT_NAME varchar(255) NULL,
	EMPLOYEE_APPLICATION_ID bigint NOT NULL
);

INSERT dbo.EMPLOYEE_EDUCATIONAL_QUALIFICATION VALUES
(1, 4, 'BSC', 3, 'CSE', 1),
(2, 8, 'HSC', 0, '', 2)

CREATE TABLE dbo.EMPLOYEE_APPLICATION_RULE (
	EMPLOYEE_APPLICATION_RULE_ID bigint NOT NULL,
	EDUCATION_DEGREE_ID bigint NULL,
	SUBJECT_ID bigint NULL,
	DESIGNATION_ID bigint NULL
);

INSERT dbo.EMPLOYEE_APPLICATION_RULE VALUES
(1, 4, 3, 2),
(2, 5, 10, 4)

SELECT * FROM dbo.EMPLOYEE_APPLICATION;
SELECT * FROM dbo.EMPLOYEE_EDUCATIONAL_QUALIFICATION;
SELECT * FROM dbo.EMPLOYEE_APPLICATION_RULE;


EMPLOYEE_APPLICATION_ID
EXPECTED_DESIGNATION_ID
EXPECTED_DESIGNATION_NAME
EMPLOYEE_ID


1
2
Lecturer(ICT)
100

2
4
Lecturer(ENG)
200




EMPLOYEE_EDUCATIONAL_QUALIFICATION_ID
EDUCATION_DEGREE_ID
EDUCATION_DEGREE_NAME
SUBJECT_ID
SUBJECT_NAME
EMPLOYEE_APPLICATION_ID


1
4
BSC
3
CSE
1

2
8
HSC
0

2




EMPLOYEE_APPLICATION_RULE_ID
EDUCATION_DEGREE_ID
SUBJECT_ID
DESIGNATION_ID


1
4
3
2

2
5
10
4


CREATE FUNCTION fnApplicationValidationFlags(
    @designationId BIGINT,
    @applicationId BIGINT
)
RETURNS @OutputTable TABLE (
    isRequiredSubject BIT,
    isRequiredDegree BIT,
    isQualified BIT
)
AS
BEGIN
    DECLARE @ruleSubjectId INT;
    DECLARE @ruleDegreeId INT;

    DECLARE @isRequiredSubject BIT = 1;
	DECLARE @isRequiredDegree BIT = 1;
	DECLARE @isQualified BIT = 1;

    DECLARE validation_cursor CURSOR FOR
        SELECT 
        	ear.SUBJECT_ID,
        	ear.EDUCATION_DEGREE_ID
        FROM dbo.EMPLOYEE_APPLICATION_RULE ear
        WHERE ear.DESIGNATION_ID = @designationId;

    OPEN validation_cursor;
    FETCH NEXT FROM validation_cursor INTO @ruleSubjectId, @ruleDegreeId;

    WHILE @@FETCH_STATUS = 0
    BEGIN

	--subject check
	IF @ruleSubjectId IS NULL 
	BEGIN
        SET @isRequiredSubject = 1;
    END
        ELSE
        BEGIN
	        IF EXISTS (
	            SELECT x.SUBJECT_ID
	            FROM (
	                SELECT eeq.SUBJECT_ID
	                FROM EMPLOYEE_EDUCATIONAL_QUALIFICATION eeq
	                WHERE eeq.EMPLOYEE_APPLICATION_ID = @applicationId
	            ) AS x
	            WHERE x.SUBJECT_ID IN (@ruleSubjectId)
	        )
	        BEGIN
	            SET @isRequiredSubject = 1;
	        END
	        ELSE
	        BEGIN
	            SET @isRequiredSubject = 0;
	        END
        END

        
    --degree check
    IF @ruleDegreeId IS NULL 
	BEGIN
        SET @isRequiredDegree = 1;
    END
        ELSE
        BEGIN
	        IF EXISTS (
	            SELECT x.EDUCATION_DEGREE_ID
	            FROM (
	                SELECT eeq.EDUCATION_DEGREE_ID
	                FROM EMPLOYEE_EDUCATIONAL_QUALIFICATION eeq
	                WHERE eeq.EMPLOYEE_APPLICATION_ID = @applicationId
	            ) AS x
	            WHERE x.EDUCATION_DEGREE_ID IN (@ruleDegreeId)
	        )
	        BEGIN
	            SET @isRequiredDegree = 1;
	        END
	        ELSE
	        BEGIN
	            SET @isRequiredDegree = 0;
	        END
		END
		
        FETCH NEXT FROM validation_cursor INTO @ruleSubjectId, @ruleDegreeId;
    END

    CLOSE validation_cursor;
    DEALLOCATE validation_cursor;
   
	IF  @isRequiredSubject = 1 AND @isRequiredDegree = 1
    BEGIN
        SET @isQualified = 1;
    END
    ELSE
    BEGIN
        SET @isQualified = 0;
    END
   	   
    INSERT INTO @OutputTable (isRequiredSubject, isRequiredDegree, isQualified)
    VALUES (@isRequiredSubject, @isRequiredDegree, @isQualified);
    RETURN;

END;
SELECT
    ea.EMPLOYEE_APPLICATION_ID,
    applicationValidationFlags.isRequiredSubject,
    applicationValidationFlags.isRequiredDegree,
    applicationValidationFlags.isQualified
FROM EMPLOYEE_APPLICATION ea
CROSS APPLY dbo.fnApplicationValidationFlags(
        ea.EXPECTED_DESIGNATION_ID,
        ea.EMPLOYEE_APPLICATION_ID
    ) AS applicationValidationFlags


EMPLOYEE_APPLICATION_ID
isRequiredSubject
isRequiredDegree
isQualified


1
True
True
True

2
False
False
False