SELECT
SCHOOLYEAR,
SEMESTER,
STUDYPROGRAMNAME,
STUDENTID NIM,
FULLNAME NAMA,
PLONUMBER NOMOR_PLO,
PLONAME TEXT_PLO,
SUBJECTCODE KODE_MK,
SUBJECTNAME NAMA_MK,
A.COURSEID,
A.CLASS,
A.LECTURERCODE,
CLONUMBER NOMOR_CLO,
CLONAME TEXT_CLO,
ROUND( ( ASSESSMENTOOLSCORE * ( PERCENTAGE / 100 ) ), 2 ) AS NILAI_CLO
FROM
(
SELECT DISTINCT
A.PLONUMBER,
A.PLONAME,
B.STUDYPROGRAMID,
B.STUDYPROGRAMNAME,
D.SUBJECTCODE,
H.SUBJECTNAME,
C.CLONUMBER,
C.CLONAME,
E.COURSEID,
F.CLASS,
F.LECTURERCODE,
E.STUDENTID,
D.SCHOOLYEAR,
D.SEMESTER,
E.CLOASSESMENTID,
G.PERCENTAGE,
G.CLOASSESSMENTTOOLSNAME,
SUM( E.NILAIPERQUEST ) AS ASSESSMENTOOLSCORE
FROM
ACADEMIC.CLOPLO A
LEFT JOIN MASTERDATA.STUDYPROGRAM B ON ( A.STUDYPROGRAMID = B.STUDYPROGRAMID )
LEFT JOIN ACADEMIC.CLOCLO C ON ( A.CLOPLOID = C.CLOPLOID AND C.ACTIVESTATUS = 'Y' )
LEFT JOIN ACADEMIC.CLOMAPPING D ON ( C.CLOCLOID = D.CLOCLOID AND D.ACTIVESTATUS = 'Y' )
LEFT JOIN (
SELECT DISTINCT
A.CLOSTUDENTPOINTID,
A.COURSEID,
A.CLOQUESTIONID,
A.CLOPOINT,
A.INPUTBY,
A.INPUTDATE,
A.STUDENTID,
B.QUESTIONDESCRIPTION,
B.PERCENTAGE,
B.CLOMAPPINGID,
B.CLOASSESMENTID,
A.CLOPOINT * ( B.PERCENTAGE / 100 ) AS NILAIPERQUEST
FROM
ACADEMIC.CLOSTUDENTPOINT A
JOIN ACADEMIC.CLOQUESTION B ON ( A.CLOQUESTIONID = B.CLOQUESTIONID )
WHERE
A.STUDENTID IS NOT NULL
AND A.COURSEID IS NOT NULL
) E ON ( D.CLOMAPPINGID = E.CLOMAPPINGID )
LEFT JOIN ACADEMIC.COURSE F ON ( E.COURSEID = F.COURSEID )
LEFT JOIN ACADEMIC.SUBJECTS H ON ( F.SUBJECTID = H.SUBJECTID )
LEFT JOIN ACADEMIC.CLOASSESSMENTTOOLS G ON ( E.CLOASSESMENTID = G.CLOASSESSMENTTOOLSID )
WHERE
A.ACTIVESTATUS = 'Y'
AND D.SCHOOLYEAR = '2526'
AND D.SEMESTER = 1
AND A.ACTIVESTATUS = 'Y'
GROUP BY
A.PLONUMBER,
A.PLONAME,
B.STUDYPROGRAMID,
B.STUDYPROGRAMNAME,
D.SUBJECTCODE,
H.SUBJECTNAME,
C.CLONUMBER,
C.CLONAME,
E.CLOASSESMENTID,
G.PERCENTAGE,
G.CLOASSESSMENTTOOLSNAME,
E.COURSEID,
F.CLASS,
F.LECTURERCODE,
E.STUDENTID,
D.SCHOOLYEAR,
D.SEMESTER
) A
JOIN MASTERDATA.STUDENT USING ( STUDENTID )
WHERE
STUDENTID IS NOT NULL

ORDER BY
COURSEID,
STUDENTID,
PLONUMBER,
CLONUMBER