 
create view vw_subjects_home_teacher AS
(SELECT 
    `cat_subject`.`name_csb`,
    `subject_course`.`serial_sbc` AS `serial_subject`,
    `course`.`serial_crs` AS `course_or_level`,
    'MANDATORY' AS type_subject,
    `level`.`name_lvl`,
    `credits_detail`.`start_at_cdt`,
    `credits_detail`.`end_at_cdt`,
    `schedule_header`.`date_start_shd`,
    `schedule_header`.`date_end_shd`,
    (select count(distinct(stc.serial_std))
			from student_by_course stc 
			join student_year sty on sty.serial_std=stc.serial_std
					AND stc.status_stc='ACTIVE'
					AND sty.status_sty !='RETIRED'
					AND (sty.comment_sty is null or sty.comment_sty  !='**LEVEL CHANGE**')
                    AND stc.serial_crs=course.serial_crs
            join student std on std.serial_std=sty.serial_std
					AND std.gender_std='MALE'
    ) as total_students_male,
    (select count(distinct(stc.serial_std))
			from student_by_course stc 
			join student_year sty on sty.serial_std=stc.serial_std
					AND stc.status_stc='ACTIVE'
					AND sty.status_sty !='RETIRED'
					AND (sty.comment_sty is null or sty.comment_sty  !='**LEVEL CHANGE**')
                    AND stc.serial_crs=course.serial_crs
            join student std on std.serial_std=sty.serial_std
					AND std.gender_std='FEMALE'
    ) as total_students_female
FROM
    `cat_subject`
        INNER JOIN
    `subject` ON `subject`.`serial_csb` = `cat_subject`.`serial_csb`
        INNER JOIN
    `subject_course` ON `subject_course`.`serial_sbj` = `subject`.`serial_sbj`
        AND `subject_course`.`serial_emp` = 527
        AND `subject_course`.`status_sbc` = 'ACTIVE'
        AND `subject`.`status_sbj` = 'ACTIVE'
        AND `cat_subject`.`status_csb` = 'ACTIVE'
        INNER JOIN
    `course` ON `course`.`serial_crs` = `subject_course`.`serial_crs`
        AND `course`.`serial_scy` = 5
        INNER JOIN
    `level` ON `level`.`serial_lvl` = `course`.`serial_lvl`
        INNER JOIN
    `class_group` ON `class_group`.`serial_clg` = `course`.`serial_clg`
        INNER JOIN
    `schedule` ON `schedule`.`serial_sbc` = `subject_course`.`serial_sbc`
        AND `schedule`.`day_scd` = 'MONDAY'
        AND `schedule`.`status_scd` = 'ACTIVE'
        AND schedule.serial_emp = subject_course.serial_emp
        INNER JOIN
    `schedule_header` ON `schedule_header`.`serial_shd` = `schedule`.`serial_shd`
        AND `schedule_header`.`status_shd` = 'ACTIVE'
        AND `schedule_header`.`in_use_shd` = 1
        INNER JOIN
    `credits_detail` ON `credits_detail`.`serial_cdt` = `schedule`.`serial_cdt`) UNION (SELECT 
    `cat_subject`.`name_csb`,
    `optional_subject`.`serial_osb` AS `serial_subject`,
    `optional_subject_level`.`serial_lvl` AS `course_or_level`,
    'MANDATORY' AS type_subject,
    `level`.`name_lvl`,
    `credits_detail`.`start_at_cdt`,
    `credits_detail`.`end_at_cdt`,
    `schedule_header`.`date_start_shd`,
    `schedule_header`.`date_end_shd`,
    (select count(distinct(sos.serial_std))
		from student_optional_subject sos
		join period prd on prd.serial_prd=sos.serial_prd
			AND prd.start_date_prd <= now() 
			AND prd.end_date_prd >= now() 
            AND sos.serial_osb=optional_subject.serial_osb
        join student_year sty on sty.serial_std=sos.serial_std
			AND sty.status_sty !='RETIRED'
			AND (sty.comment_sty is null or sty.comment_sty  !='**LEVEL CHANGE**')
        join student std on std.serial_std=sty.serial_std
			AND std.gender_std='MALE'    
		where status_sos='ACTIVE'
    ) as total_students_male,
    (select count(distinct(sos.serial_std))
		from student_optional_subject sos
		join period prd on prd.serial_prd=sos.serial_prd
			AND prd.start_date_prd <= now() 
			AND prd.end_date_prd >= now() 
            AND sos.serial_osb=optional_subject.serial_osb
        join student_year sty on sty.serial_std=sos.serial_std
			AND sty.status_sty !='RETIRED'
			AND (sty.comment_sty is null or sty.comment_sty  !='**LEVEL CHANGE**')
        join student std on std.serial_std=sty.serial_std
			AND std.gender_std='FEMALE'    
		where status_sos='ACTIVE'
    ) as total_students_female
FROM
    `cat_subject`
        INNER JOIN
    `optional_subject` ON `optional_subject`.`serial_csb` = `cat_subject`.`serial_csb`
        INNER JOIN
    `optional_subject_level` ON `optional_subject_level`.`serial_osb` = `optional_subject`.`serial_osb`
        INNER JOIN
    `group_optional_subject` ON `group_optional_subject`.`serial_osb` = `optional_subject`.`serial_osb`
        AND `group_optional_subject`.`status_gos` = 'ACTIVE'
        INNER JOIN
    `level_optional_group` ON `level_optional_group`.`serial_lop` = `group_optional_subject`.`serial_lop`
        AND `level_optional_group`.`status_lop` = 'ACTIVE'
        INNER JOIN
    `level` ON `level`.`serial_lvl` = `optional_subject_level`.`serial_lvl`
        INNER JOIN
    `schedule` ON `schedule`.`serial_lop` = `level_optional_group`.`serial_lop`
        AND `schedule`.`day_scd` = 'MONDAY'
        AND `schedule`.`status_scd` = 'ACTIVE'
        INNER JOIN
    `schedule_header` ON `schedule_header`.`serial_shd` = `schedule`.`serial_shd`
        AND `schedule_header`.`status_shd` = 'ACTIVE'
        AND `schedule_header`.`in_use_shd` = 1
        AND schedule.serial_emp = optional_subject.serial_emp
        INNER JOIN
    `credits_detail` ON `credits_detail`.`serial_cdt` = `schedule`.`serial_cdt`
WHERE
    `optional_subject_level`.`status_osl` = 'ACTIVE'
        AND `optional_subject`.`status_osb` = 'ACTIVE'
        AND `cat_subject`.`status_csb` = 'ACTIVE'
        AND `optional_subject`.`serial_emp` = 527) ORDER BY `start_at_cdt` ASC
;