Show remarks in sql based on comparing five rounds of examination

Hi smile,

Please refer below sql query.

CREATE TABLE tblTestSystem
(
    AdmissionNo INT,
    ClassID     INT,
    SubjectID   INT,
    RoundID     INT,
    Marks       INT,
    TotalMarks  INT,
    TestDate    VARCHAR(50)
)
TRUNCATE TABLE tblTestSystem
INSERT INTO tblTestSystem VALUES(1,101,201,1,5,10,'1/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,201,1,7,10,'1/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,201,1,8,10,'1/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,202,1,7,10,'2/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,202,1,2,10,'2/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,202,1,5,10,'2/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,203,1,9,10,'3/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,203,1,6,10,'3/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,203,1,6,10,'3/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,201,2,5,10,'4/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,201,2,7,10,'4/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,201,2,9,10,'4/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,202,2,6,10,'5/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,202,2,6,10,'5/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,202,2,5,10,'5/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,203,2,9,10,'6/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,203,2,8,10,'6/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,203,2,7,10,'6/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,201,3,5,10,'7/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,201,3,4,10,'7/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,201,3,8,10,'7/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,202,3,7,10,'8/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,202,3,2,10,'8/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,202,3,5,10,'8/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,203,3,9,10,'9/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,203,3,3,10,'9/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,203,3,7,10,'9/3/2023')
 
SELECT t1.admissionno,t1.classid,CONCAT(t2.SubjectName,'(',t1.TotalMarks,')') AS subjectid,t1.roundid,t1.marks,t1.totalmarks,t1.testdate  into tblTestSystem2 FROM  tblTestSystem t1 join tblDefSubjects t2 ON t2.SubjectId = t1.SubjectID

DECLARE @DynamicPivotQuery2 AS NVARCHAR(MAX)
DECLARE @ColumnName2 AS NVARCHAR(MAX)
SELECT @ColumnName2 = ISNULL(@ColumnName2 + ',','')+ QUOTENAME(SubjectID) FROM (SELECT DISTINCT SubjectID FROM tblTestSystem2 ) AS FeesHead
 
SET @DynamicPivotQuery2 = '
;WITH CTE AS(
    SELECT RoundID,fa.AdmissionNo,Marks,SubjectID,ClassID
    ,(SELECT SUM(tsm.Marks) FROM tblTestSystem2 tsm where tsm.AdmissionNo = fa.AdmissionNo AND tsm.RoundID = fa.RoundID ) as Obtain
    ,(SELECT SUM(tsm.TotalMarks) FROM tblTestSystem2 tsm where tsm.AdmissionNo = fa.AdmissionNo AND tsm.RoundID = fa.RoundID) as Total
    FROM tblTestSystem2 as fa
)
SELECT AdmissionNo,ClassID,RoundID,'+@ColumnName2+',Obtain,Total INTO temp1 FROM CTE
PIVOT (MAX(Marks)
FOR SubjectID IN('+@ColumnName2+')) p order by AdmissionNo asc'
EXEC(@DynamicPivotQuery2);

SELECT DISTINCT AdmissionNo,
	CASE
		WHEN ((SELECT SUM(Obtain) FROM temp1 WHERE RoundID < 3 and AdmissionNo=1) <= (SELECT SUM(Obtain) FROM temp1 WHERE RoundID > 1 and AdmissionNo=1)) and AdmissionNo = 1  THEN 'He is improving positively'
		WHEN ((SELECT SUM(Obtain) FROM temp1 WHERE RoundID < 3 and AdmissionNo=2) <= (SELECT SUM(Obtain) FROM temp1 WHERE RoundID > 1 and AdmissionNo=2)) and AdmissionNo = 2  THEN 'He is improving positively'
		WHEN ((SELECT SUM(Obtain) FROM temp1 WHERE RoundID < 3 and AdmissionNo=3) <= (SELECT SUM(Obtain) FROM temp1 WHERE RoundID > 1 and AdmissionNo=3)) and AdmissionNo = 3  THEN 'He is improving positively'
		ELSE 'His perfomance is decreasing '
	END AS Remark
FROM temp1

Output

temp Table

Output2