JW/EMIS.Web/Config/Report/StudentManage/StudentCountView.sql

select 
schoolyear.Code as SchoolyearCode,
classCount.*,
StudentMaleCount.StudentMaleCount,
StudentFemaleCount.StudentFemaleCount
from (
select 
campus.CampusID,
campus.No as CampusNo,
campus.Name as CampusName,
college.CollegeID,
college.No as CollegeNo,
college.Name as CollegeName,
faculty.StandardID,
[standard].Name as StandardName,
grade.GradeID as GradeYear,
faculty.EducationID,
education.Name as EducationName,
student.StudentType,
studentType.Name as StudentTypeName,
COUNT(distinct class.ClassmajorID) as ClassmajorCount
from [dbo].CF_Campus campus
inner join [dbo].[CF_College] college on college.CampusID =campus.CampusID
inner join [dbo].[CF_Facultymajor] faculty on college.CollegeID=faculty.CollegeID 
inner join [dbo].[CF_Grademajor] grade on faculty.FacultymajorID=grade.FacultymajorID 
inner join [dbo].[CF_Classmajor] class on grade.GrademajorID=class.GrademajorID 
inner join [dbo].[CF_Student] student on class.ClassmajorID=student.ClassmajorID 
inner join [dbo].[Sys_DictionaryItem] [standard] on faculty.StandardID=[standard].Value and [standard].DictionaryCode='CF_Standard'
inner join [dbo].[Sys_DictionaryItem] education on faculty.EducationID=education.Value and education.DictionaryCode='CF_Education'
inner join [dbo].[Sys_DictionaryItem] studentType on student.StudentType=studentType.Value and studentType.DictionaryCode='CF_STUDENTTYPE'
left join [dbo].[CF_InSchoolSetting] inschoolset on student.InSchoolStatusID=inschoolset.InSchoolStatusID 
where 
((@InSchoolStatusID is null or @InSchoolStatusID=0) 
or (case when @InSchoolStatusID=1 then 1 else 0 end=ISNULL(inschoolset.IsSelected,0))) 
group by campus.CampusID,campus.No,campus.Name,college.CollegeID,college.No,college.Name,faculty.StandardID,[standard].Name,grade.GradeID,faculty.EducationID,education.Name,student.StudentType,studentType.Name) classCount 
left join 
(select 
campus.CampusID,
campus.Name as CampusName,
college.CollegeID,
college.Name as CollegeName,
faculty.StandardID,
[standard].Name as StandardName,
grade.GradeID as GradeYear,
faculty.EducationID,
education.Name as EducationName,
student.StudentType,
studentType.Name as StudentTypeName,
student.SexID AS Sex,
sex.Name as SexName,
COUNT(distinct student.UserID) as StudentMaleCount
from [dbo].CF_Campus campus
inner join [dbo].[CF_College] college on college.CampusID =campus.CampusID
inner join [dbo].[CF_Facultymajor] faculty on college.CollegeID=faculty.CollegeID 
inner join [dbo].[CF_Grademajor] grade on faculty.FacultymajorID=grade.FacultymajorID 
inner join [dbo].[CF_Classmajor] class on grade.GrademajorID=class.GrademajorID 
inner join [dbo].[CF_Student] student on class.ClassmajorID=student.ClassmajorID 
inner join [dbo].[Sys_DictionaryItem] [standard] on faculty.StandardID=[standard].Value and [standard].DictionaryCode='CF_Standard'
inner join [dbo].[Sys_DictionaryItem] education on faculty.EducationID=education.Value and education.DictionaryCode='CF_Education'
inner join [dbo].[Sys_DictionaryItem] sex on student.SexID=sex.Value and sex.DictionaryCode='CF_Sex'
inner join [dbo].[Sys_DictionaryItem] studentType on student.StudentType=studentType.Value and studentType.DictionaryCode='CF_STUDENTTYPE'
left join [dbo].[CF_InSchoolSetting] inschoolset on student.InSchoolStatusID=inschoolset.InSchoolStatusID 
where ((@InSchoolStatusID is null or @InSchoolStatusID=0) 
or (case when @InSchoolStatusID=1 then 1 else 0 end=ISNULL(inschoolset.IsSelected,0))) 
group by campus.CampusID,campus.Name,college.CollegeID,college.Name,faculty.StandardID,[standard].Name,grade.GradeID,
faculty.EducationID,education.Name,student.StudentType,studentType.Name,student.SexID,sex.Name,sex.Code having sex.Code='Male'
) studentMaleCount
on classCount.CampusID=studentMaleCount.CampusID 
and classCount.CollegeID=studentMaleCount.CollegeID 
and classCount.StandardID=studentMaleCount.StandardID 
and classCount.GradeYear=studentMaleCount.GradeYear 
and classCount.EducationID=studentMaleCount.EducationID 
and classCount.StudentType=studentMaleCount.StudentType
left join 
(select campus.CampusID,campus.Name as CampusName,college.CollegeID,college.Name as CollegeName,
faculty.StandardID,[standard].Name as StandardName,grade.GradeID as GradeYear,
faculty.EducationID,education.Name as EducationName,student.StudentType,studentType.Name as StudentTypeName,
student.SexID AS Sex,sex.Name as SexName,
COUNT(distinct student.UserID) as StudentFemaleCount
from [dbo].CF_Campus campus
inner join [dbo].[CF_College] college on college.CampusID =campus.CampusID
inner join [dbo].[CF_Facultymajor] faculty on college.CollegeID=faculty.CollegeID 
inner join [dbo].[CF_Grademajor] grade on faculty.FacultymajorID=grade.FacultymajorID 
inner join [dbo].[CF_Classmajor] class on grade.GrademajorID=class.GrademajorID 
inner join [dbo].[CF_Student] student on class.ClassmajorID=student.ClassmajorID 
inner join [dbo].[Sys_DictionaryItem] [standard] on faculty.StandardID=[standard].Value and [standard].DictionaryCode='CF_Standard'
inner join [dbo].[Sys_DictionaryItem] education on faculty.EducationID=education.Value and education.DictionaryCode='CF_Education'
inner join [dbo].[Sys_DictionaryItem] sex on student.SexID=sex.Value and sex.DictionaryCode='CF_Sex'
inner join [dbo].[Sys_DictionaryItem] studentType on student.StudentType=studentType.Value and studentType.DictionaryCode='CF_STUDENTTYPE'
left join [dbo].[CF_InSchoolSetting] inschoolset on student.InSchoolStatusID=inschoolset.InSchoolStatusID 
where ((@InSchoolStatusID is null or @InSchoolStatusID=0) 
or (case when @InSchoolStatusID=1 then 1 else 0 end=ISNULL(inschoolset.IsSelected,0))) 
group by campus.CampusID,campus.Name,college.CollegeID,college.Name,faculty.StandardID,[standard].Name,grade.GradeID,
faculty.EducationID,education.Name,student.StudentType,studentType.Name,student.SexID,sex.Name,sex.Code having sex.Code='Female'
) studentFemaleCount
on classCount.CampusID=studentFemaleCount.CampusID 
and classCount.CollegeID=studentFemaleCount.CollegeID 
and classCount.StandardID=studentFemaleCount.StandardID 
and classCount.GradeYear=studentFemaleCount.GradeYear 
and classCount.EducationID=studentFemaleCount.EducationID 
and classCount.StudentType=studentFemaleCount.StudentType
inner join CF_Schoolyear schoolyear on schoolyear.IsCurrent=1 
order by classCount.CampusNo,classCount.CollegeNo