select distinct '1 Identified District as '+eo.NameOfInstitution as Message from edfi.CalendarDate cd left join edfi.School sch on sch.SchoolId=cd.SchoolId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=sch.LocalEducationAgencyId union select distinct (case when syt.SchoolYearDescription='2021-2022' then '1f Correct School year 2021-2022' else '1f *** Incorrrect school year '+syt.SchoolYearDescription+'- Please run this script on 2021-2022 *** ' end) as Message from edfi.Section sec left join edfi.SchoolYearType syt on syt.SchoolYear=sec.SchoolYear and syt.CurrentSchoolYear=1 union select (case when (select count(*) from edfi.Student) > 0 then '1a Student table is populated' else '1a *** Student table not populated ***' end) as Message union select (case when (select count(*) from edfi.StudentSectionAssociation) > 0 then '1b StudentSectionAssociation table is populated' else '1b *** StudentSectionAssociation table not populated ***' end) as Message union select (case when (select count(*) from edfi.Staff) > 0 then '1c Staff table is populated' else '1c *** Staff table not populated ***' end) as Message union select (case when (select count(*) from edfi.StaffSectionAssociation) > 0 then '1d StaffSectionAssociation table is populated' else '1d *** StaffSectionAssociation table not populated ***' end) as Message union select (case when (select count(*) from edfi.Course) > 0 then '1e Course table is populated' else '1e *** Course table not populated ***' end) as Message union select distinct '2ai *** Invalid UIC for student '+s.LastSurname+', '+s.Firstname+' '+sic.IdentificationCode as Message from edfi.StudentSectionAssociation ssa left join edfi.Student s on s.StudentUSI=ssa.StudentUSI left join edfi.StudentEducationOrganizationAssociationStudentIdentificationCode sic on sic.StudentUSI=s.StudentUSI left join edfi.Descriptor d on d.DescriptorId=sic.StudentIdentificationSystemDescriptorId where (len(sic.IdentificationCode) <> 10 or isnumeric(sic.IdentificationCode) =0 )and d.CodeValue='State' union select distinct '2aii *** Missing UIC for: '+s.LastSurname+', '+s.FirstName as Message from edfi.StudentSectionAssociation ssa left join edfi.Student s on ssa.StudentUSI=s.StudentUSI where ssa.StudentUSI not in (select distinct s.StudentUSI from edfi.Student s left join edfi.StudentEducationOrganizationAssociationStudentIdentificationCode sic on sic.StudentUSI=s.StudentUSI left join edfi.Descriptor d on d.DescriptorId=sic.StudentIdentificationSystemDescriptorId where d.CodeValue='State') union select distinct '2bi *** Invalid PIC for staff '+s.LastSurname+', '+s.Firstname+' '+sic.IdentificationCode as Message from edfi.StaffSectionAssociation ssa left join edfi.Staff s on ssa.StaffUSI=s.StaffUSI left join edfi.StaffIdentificationCode sic on sic.StaffUSI=s.StaffUSI left join edfi.Descriptor d on d.DescriptorId=sic.StaffIdentificationSystemDescriptorId where d.CodeValue='State' and (len(sic.IdentificationCode)<2 or len(sic.IdentificationCode)>10 or ISNUMERIC(sic.IdentificationCode)=0) union select distinct '2bii Missing PIC for: '+s.LastSurname+', '+s.FirstName as Message from edfi.StaffSectionAssociation ssa left join edfi.Staff s on ssa.StaffUSI=s.StaffUSI where ssa.StaffUSI not in (select distinct s.StaffUSI from edfi.Staff s left join edfi.StaffIdentificationCode sic on sic.StaffUSI=s.StaffUSI left join edfi.Descriptor d on d.DescriptorId=sic.StaffIdentificationSystemDescriptorId where d.CodeValue='State') union select distinct '3a *** Course '+c.CourseCode+' - '+isnull(c.CourseTitle,'') + 'Has no title' from edfi.Section sec left join edfi.CourseOffering co on co.SchoolId=sec.SchoolId and co.SchoolYear=sec.SchoolYear and co.LocalCourseCode=sec.LocalCourseCode and co.SessionName=sec.SessionName left join edfi.Course c on c.EducationOrganizationId=co.EducationOrganizationId and c.CourseCode=co.CourseCode where c.CourseTitle is null or c.CourseTitle='' union select distinct '3ci *** No staff for '+c.CourseCode+' '+c.CourseTitle +' '+ssa.SessionName+' School '+eo.NameOfInstitution as Message from edfi.StudentSectionAssociation ssa left join edfi.StaffSectionAssociation stsa on stsa.SchoolId=ssa.SchoolId and stsa.SchoolYear=ssa.SchoolYear and stsa.LocalCourseCode=ssa.LocalCourseCode and stsa.SectionIdentifier=ssa.SectionIdentifier and stsa.SessionName=ssa.SessionName left join edfi.CourseOffering co on co.LocalCourseCode=ssa.LocalCourseCode and co.SchoolId=ssa.SchoolId and co.SchoolYear=ssa.SchoolYear and co.SessionName=ssa.SessionName left join edfi.Course c on c.CourseCode=co.CourseCode and c.EducationOrganizationId=co.EducationOrganizationId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId where stsa.StaffUSI is null union select distinct '3ci *** No students for '+c.CourseCode+' '+c.CourseTitle +' '+ssa.SessionName+' School '+eo.NameOfInstitution as Message from edfi.StaffSectionAssociation ssa left join edfi.StudentSectionAssociation stsa on stsa.SchoolId=ssa.SchoolId and stsa.SchoolYear=ssa.SchoolYear and stsa.LocalCourseCode=ssa.LocalCourseCode and stsa.SectionIdentifier=ssa.SectionIdentifier and stsa.SessionName=ssa.SessionName left join edfi.CourseOffering co on co.LocalCourseCode=ssa.LocalCourseCode and co.SchoolId=ssa.SchoolId and co.SchoolYear=ssa.SchoolYear and co.SessionName=ssa.SessionName left join edfi.Course c on c.CourseCode=co.CourseCode and c.EducationOrganizationId=co.EducationOrganizationId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId where stsa.StudentUSI is null union select '3cii *** No Staff Section Associations for ***'+convert(nvarchar(10),eo.EducationOrganizationId)+'-'+eo.NameOfInstitution as Message from edfi.School sch left join edfi.EducationOrganization eo on eo.EducationOrganizationId=sch.SchoolId where (select count(*) from edfi.StaffSectionAssociation ssa where ssa.SchoolId=sch.SchoolId)=0 union select '3cii *** No Student Section Associations for ***'+convert(nvarchar(10),eo.EducationOrganizationId)+'-'+eo.NameOfInstitution as Message from edfi.School sch left join edfi.EducationOrganization eo on eo.EducationOrganizationId=sch.SchoolId where (select count(*) from edfi.StudentSectionAssociation ssa where ssa.SchoolId=sch.SchoolId)=0 union select distinct '4a **** Missing Subject for Course: '+c.CourseCode+' '+c.CourseTitle+' School: '+eo.NameOfInstitution as Message from edfi.StudentSectionAssociation ssa left join edfi.CourseOffering co on co.LocalCourseCode=ssa.LocalCourseCode and co.SchoolId=ssa.SchoolId and co.SchoolYear=ssa.SchoolYear and co.SessionName=ssa.SessionName left join edfi.Course c on c.CourseCode=co.CourseCode and c.EducationOrganizationId=co.EducationOrganizationId left join edfi.Descriptor at on at.DescriptorId=c.AcademicSubjectDescriptorId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId where at.ShortDescription is null union select distinct '5a **** Missing student section begin or end date for Student: ' +s.LastSurname+', '+s.FirstName +' Course: '+c.CourseCode+' '+c.CourseTitle +' Section: '+ssa.SectionIdentifier +' Session: '+ssa.SessionName +' School: '+eo.NameOfInstitution as Message from edfi.StudentSectionAssociation ssa left join edfi.CourseOffering co on co.LocalCourseCode=ssa.LocalCourseCode and co.SchoolId=ssa.SchoolId and co.SchoolYear=ssa.SchoolYear and co.SessionName=ssa.SessionName left join edfi.Course c on c.CourseCode=co.CourseCode and c.EducationOrganizationId=co.EducationOrganizationId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId left join edfi.Student s on s.StudentUSI=ssa.StudentUSI where ssa.BeginDate is null or ssa.EndDate is null union select distinct '5a **** Missing staff section begin or end date for Staff: ' +s.LastSurname+', '+s.FirstName +' Course: '+c.CourseCode+' '+c.CourseTitle +' Section: '+ssa.SectionIdentifier +' Session: '+ssa.SessionName +' School: '+eo.NameOfInstitution as Message from edfi.StaffSectionAssociation ssa left join edfi.CourseOffering co on co.LocalCourseCode=ssa.LocalCourseCode and co.SchoolId=ssa.SchoolId and co.SchoolYear=ssa.SchoolYear and co.SessionName=ssa.SessionName left join edfi.Course c on c.CourseCode=co.CourseCode and c.EducationOrganizationId=co.EducationOrganizationId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId left join edfi.Staff s on s.StaffUSI=ssa.StaffUSI where ssa.BeginDate is null or ssa.EndDate is null union select distinct '5b **** No overlap for Staff: ' +s.LastSurname+', '+s.FirstName +' and Student: ' +s2.LastSurname+', '+s2.FirstName +' For Course: '+c.CourseCode+' '+c.CourseTitle +' Section: '+ssa.SectionIdentifier +' Session: '+ssa.SessionName +' School: '+eo.NameOfInstitution as Message from edfi.StaffSectionAssociation ssa left join edfi.StudentSectionAssociation ssa2 on ssa.SchoolId=ssa2.SchoolId and ssa.SchoolYear=ssa2.SchoolYear and ssa.LocalCourseCode=ssa2.LocalCourseCode and ssa.SectionIdentifier=ssa2.SectionIdentifier and ssa.SessionName=ssa2.SessionName left join edfi.CourseOffering co on co.LocalCourseCode=ssa.LocalCourseCode and co.SchoolId=ssa.SchoolId and co.SchoolYear=ssa.SchoolYear and co.SessionName=ssa.SessionName left join edfi.Course c on c.CourseCode=co.CourseCode and c.EducationOrganizationId=co.EducationOrganizationId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId left join edfi.Staff s on s.StaffUSI=ssa.StaffUSI left join edfi.Student s2 on s2.StudentUSI=ssa2.StudentUSI where ssa2.EndDate < ssa.BeginDate or ssa2.BeginDate > ssa.EndDate union select '6 No Staff Email for '+s.LastSurname+', '+s.FirstName from edfi.StaffSectionAssociation ssa left join edfi.StaffElectronicMail sem on sem.StaffUSI=ssa.StaffUSI left join edfi.Staff s on s.StaffUSI=ssa.StaffUSI where sem.ElectronicMailAddress is null union select '7 '+eo.NameOfInstitution+(case when (select count(*) from edfi.StudentSchoolAssociation ssa left join edfi.Descriptor gld on gld.DescriptorId=ssa.EntryGradeLevelDescriptorId where gld.ShortDescription in ('Third grade','Fourth grade','Fifth grade','Sixth grade','Seventh Grade') and ssa.SchoolId=sch.SchoolId)>0 then ' has appropriate Grade Levels for EVAAS' else ' does not EVAAS grade levels 3-7 ***' end ) as Message from edfi.School sch left join edfi.EducationOrganization eo on eo.EducationOrganizationId=sch.SchoolId union select '7 '+eo.NameOfInstitution+' has '+ convert(nvarchar(5),(select Count(distinct c.CourseCode) from edfi.Section sec left join edfi.CourseOffering co on co.LocalCourseCode=sec.LocalCourseCode and co.SchoolId=sec.SchoolId and co.SchoolYear=sec.SchoolYear and co.SessionName=sec.SessionName left join edfi.Course c on c.CourseCode=co.CourseCode and co.EducationOrganizationId=c.EducationOrganizationId left join edfi.Descriptor asd on asd.DescriptorId=c.AcademicSubjectDescriptorId where asd.ShortDescription='Mathematics' and co.SchoolId=sch.SchoolId)) + ' Math and '+ convert(nvarchar(5),(select Count(distinct c.CourseCode) from edfi.Section sec left join edfi.CourseOffering co on co.LocalCourseCode=sec.LocalCourseCode and co.SchoolId=sec.SchoolId and co.SchoolYear=sec.SchoolYear and co.SessionName=sec.SessionName left join edfi.Course c on c.CourseCode=co.CourseCode and co.EducationOrganizationId=c.EducationOrganizationId left join edfi.Descriptor asd on asd.DescriptorId=c.AcademicSubjectDescriptorId where (asd.ShortDescription='English Language Arts' or asd.ShortDescription='English language and literature') and co.SchoolId=sch.SchoolId))+' ELA courses for consideration' as Message from edfi.School sch left join edfi.EducationOrganization eo on eo.EducationOrganizationId=sch.SchoolId order by 1