SELECT scy.serial_scy, scy.name_scy ,crs.serial_crs, stc.serial_std,std.first_name_std,std.last_name_std,sbc.serial_sbc,CONCAT(IF((csb2.name_csb IS NULL),'',CONCAT(csb2.name_csb,'-')),csb.name_csb) as subject_name, prd.serial_prd, prd.name_prd, scg.serial_act,act.due_date_act,scg.serial_att,SUM(scg.activity_grade_part_scg) as act_grade
FROM school_year scy 
JOIN course crs ON crs.serial_scy = scy.serial_scy AND crs.status_crs = 'ACTIVE' 
JOIN student_by_course stc ON stc.serial_crs = crs.serial_crs AND stc.status_stc = 'ACTIVE'
JOIN subject_course sbc ON sbc.serial_crs = crs.serial_crs AND sbc.status_sbc = 'ACTIVE' 
JOIN subject sbj ON sbj.serial_sbj= sbc.serial_sbj AND sbj.status_sbj='ACTIVE'
JOIN cat_subject csb ON csb.serial_csb = sbj.serial_csb AND csb.status_csb = 'ACTIVE' 
LEFT JOIN cat_subject csb2 ON csb.cat_serial_csb = csb2.serial_csb
JOIN subject_course_period scp ON scp.serial_sbc = sbc.serial_sbc AND scp.status_scp = 'ACTIVE'
JOIN period prd ON prd.serial_prd = scp.serial_prd AND prd.status_prd = 'ACTIVE'
JOIN student std ON std.serial_std = stc.serial_std
JOIN student_criteria_grade scg ON scg.serial_prd=scp.serial_prd AND scg.serial_sbc = sbc.serial_sbc AND scg.serial_std=std.serial_std AND scg.status_scg = 'ACTIVE' AND scg.is_complete_scg = 'YES'
JOIN activity act ON act.serial_act = scg.serial_act
WHERE scy.status_scy NOT LIKE '%INACTIVE%'
GROUP BY scg.serial_std,scg.serial_act,scg.serial_crs,scg.serial_sbc
ORDER BY scy.serial_scy,crs.serial_crs, stc.serial_std,subject_name,prd.serial_prd,serial_act,serial_att

