Display Total Mark, Mark Secured, Present, Absent and Leave like Result Card in SQL Server

Hi smile,

Your database structure is very confusing to understand as per your example test query.

I have created one sample test query with temporary table related to show same output from created table.

Refer below test query for your reference also implement the logic as per test query in your table structure for relevant output.

SQL

CREATE TABLE #tblDefClass(ClassId INT,ClassName VARCHAR(20))
CREATE TABLE #tblDefSection(SectionID INT,SectionName VARCHAR(20))
CREATE TABLE #tblDefSubject(SubjectID INT, SubjectName Varchar(20))
CREATE TABLE #tblAttendance (AdmissionNo VARCHAR(20),ClassID INT,SectionID INT,AttendanceDate DATETIME,AttenStatus CHAR(1))
CREATE TABLE #tblStdReg (AdmissionNo VARCHAR(20),SName VARCHAR(20),FName VARCHAR(20),RollNo INT)
CREATE TABLE #tblSetMarks(SessionId INT,ExamId INT,ClassId INT,SectionId INT,AdmissionNo VARCHAR(10),SubjectID INT,[Max] INT,Scored INT)

INSERT INTO #tblDefClass(ClassId,ClassName)
SELECT 1 , 'One'
UNION ALL
SELECT 2 , 'Two'

INSERT INTO #tblDefSection(SectionID,SectionName)
SELECT 1,'Red'
UNION ALL
SELECT 2,'Blue'

INSERT INTO #tblDefSubject(SubjectID,SubjectName)
SELECT 1,'English'
UNION ALL
SELECT 2,'Science'

INSERT INTO #tblStdReg(AdmissionNo,SName,FName,RollNo)
SELECT 'R-0001','ABC','SKY',1
UNION ALL
SELECT 'R-0002','BCD','EFG',2

INSERT INTO #tblAttendance (AdmissionNo,ClassId,SectionId,AttendanceDate,AttenStatus)
SELECT 'R-0001',1,1,'10-15-2016','P'
UNION ALL
SELECT 'R-0001',1,1,'10-16-2016','P'
UNION ALL
SELECT 'R-0001',1,1,'10-17-2016','L'
UNION ALL
SELECT 'R-0001',1,1,'10-18-2016','A'

INSERT INTO #tblSetMarks(SessionId,ExamId,ClassId,SectionId,AdmissionNo,[SubjectID],[Max],Scored)
SELECT 1,1,1,1,'R-0001',1,25,18
UNION ALL
SELECT 1,1,1,1,'R-0001',2,25,17




;WITH ctePresent
AS (
        SELECT AttenStatus,AdmissionNo,ClassId,SectionId
		FROM #tblAttendance TA
		WHERE UPPER(AttenStatus) = 'P'
),cteAbsent
AS (
        SELECT AttenStatus,AdmissionNo,ClassId,SectionId
		FROM #tblAttendance TA
		WHERE UPPER(AttenStatus) = 'A'
)
,cteLeave
AS (
        SELECT AttenStatus,AdmissionNo,ClassId,SectionId
		FROM #tblAttendance TA
		WHERE UPPER(AttenStatus) = 'L'
)
SELECT SUM([MAX]) TotalMarks
       ,  SUM(Scored) Scored
	   ,(SELECT Count(ctePresent.AttenStatus) FROM ctePresent
		 WHERE TSM.AdmissionNo = ctePresent.AdmissionNo
			AND TSM.ClassId = ctePresent.ClassId
			AND TSM.SectionId = ctePresent.SectionId) Present
	    ,( SELECT COUNT(AttenStatus)
		    FROM cteAbsent  
			WHERE TSM.AdmissionNo = cteAbsent.AdmissionNo
			AND TSM.ClassId = cteAbsent.ClassId
			AND TSM.SectionId = cteAbsent.SectionId) [Absent]
			,( SELECT COUNT(AttenStatus)
		    FROM cteLeave
			WHERE TSM.AdmissionNo = cteLeave.AdmissionNo
			AND TSM.ClassId = cteLeave.ClassId
			AND TSM.SectionId = cteLeave.SectionId) Leave
FROM #tblSetMarks TSM 
GROUP BY TSM.AdmissionNo,TSM.SessionId,TSM.ExamId,TSM.ClassId,TSM.SectionId

DROP TABLE #tblDefClass
DROP TABLE #tblDefSection
DROP TABLE #tblDefSubject
DROP TABLE #tblAttendance 
DROP TABLE #tblStdReg
DROP TABLE #tblSetMarks

Output