select distinct eo.StateOrganizationId as [School State Code] ,eo.NameOfInstitution as [School Name] ,'' as [Previous Instructor ID] ,isnull(isnull((select sic.IdentificationCode from edfi.StaffIdentificationCode sic left join edfi.Descriptor d on d.DescriptorId=sic.StaffIdentificationSystemDescriptorId where d.CodeValue='District' and sic.StaffUSI=stsec.StaffUSI),sta.StaffUniqueId),'') as [Instructor ID] ,isnull(isnull((select sic.IdentificationCode from edfi.StaffIdentificationCode sic left join edfi.Descriptor d on d.DescriptorId=sic.StaffIdentificationSystemDescriptorId where d.CodeValue='State' and sic.StaffUSI=stsec.StaffUSI),sta.StaffUniqueId),'') as [Instructor State ID] ,isnull(sta.LastSurname,'') as [Instructor Last Name] ,isnull(sta.FirstName,'') as [Instructor First Name] ,isnull(left(sta.MiddleName,1),'') as [Instructor Middle Initial] ,isnull((select sem.ElectronicMailAddress from edfi.StaffElectronicMail sem left join edfi.ElectronicMailType emt on emt.ElectronicMailTypeId=sem.ElectronicMailTypeId where sem.StaffUSI=sta.StaffUSI and emt.ShortDescription='Work'),'') as [User Name] ,isnull((select sem.ElectronicMailAddress from edfi.StaffElectronicMail sem left join edfi.ElectronicMailType emt on emt.ElectronicMailTypeId=sem.ElectronicMailTypeId where sem.StaffUSI=sta.StaffUSI and emt.ShortDescription='Work'),'') as [Email Address] ,c.CourseTitle+' T:'+isnull(dt.ShortDescription,'')+' P:'+isnull(sec.ClassPeriodName,'') as [Class Name] ,'' as [Previous Student ID] ,(select sic.IdentificationCode from edfi.StudentIdentificationCode sic left join edfi.Descriptor d on d.DescriptorId=sic.StudentIdentificationSystemDescriptorId where d.CodeValue='District' and sic.StudentUSI=s.StudentUSI) as [Student ID] ,(select sic.IdentificationCode from edfi.StudentIdentificationCode sic left join edfi.Descriptor d on d.DescriptorId=sic.StudentIdentificationSystemDescriptorId where d.CodeValue='State' and sic.StudentUSI=s.StudentUSI) as [Student State ID] ,s.LastSurname as [Student Last Name] ,s.FirstName as [Student First Name] ,isnull(left(s.MiddleName,1),'') as [Student Middle Initial] ,convert(nvarchar(10),s.BirthDate,101) as [Student Date Of Birth] ,isnull(left(st.ShortDescription,1),'') as [Student Gender] ,case glt.ShortDescription when 'Kindergarten' then '00' when 'First grade' then '01' when 'Second grade' then '02' when 'Third grade' then '03' when 'Fourth grade' then '04' when 'Fifth grade' then '05' when 'Sixth grade' then '06' when 'Seventh grade' then '07' when 'Eighth grade' then '08' when 'Ninth grade' then '09' when 'Tenth grade' then '10' when 'Eleventh grade' then '11' when 'Twelfth grade' then '12' when 'Adult Education' then '20' when 'Early Education' then '30' when 'Infant/toddler' then '30' when 'Preschool/Prekindergarten' then '30' when 'Infant/Toddler(birth thru 2 year prog)' then '30' when 'Early Childhood 2-3 yr old prg' then '30' when 'Early Childhood 3 yr old prg' then '30' when 'Pre-Kindergarten 4 yr old prg' then '30' when 'Postsecondary' then '20' when 'Ungraded' then '13' when 'Other' then '13' when 'Grade 13' then '13' else isnull(glt.ShortDescription,' ') end as [Student Grade] , case when (select count(*) from edfi.StudentRace sr where sr.StudentUSI=s.StudentUSI)>1 then 'Multi-ethnic' else isnull(rt.ShortDescription,'') end as [Student Ethnic Group Name] ,isnull((select sem.ElectronicMailAddress from edfi.StudentElectronicMail sem left join edfi.ElectronicMailType emt on emt.ElectronicMailTypeId=sem.ElectronicMailTypeId where sem.StudentUSI=s.StudentUSI and emt.ShortDescription='Organization'),'') as [Student User Name] ,isnull((select sem.ElectronicMailAddress from edfi.StudentElectronicMail sem left join edfi.ElectronicMailType emt on emt.ElectronicMailTypeId=sem.ElectronicMailTypeId where sem.StudentUSI=s.StudentUSI and emt.ShortDescription='Organization'),'') as [Student Email] from edfi.Student s left join edfi.SexType st on st.SexTypeId=s.SexTypeId left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=s.StudentUSI left join edfi.GradeLevelDescriptor gld on gld.GradeLevelDescriptorId=ssa.EntryGradeLevelDescriptorId left join edfi.GradeLevelType glt on glt.GradeLevelTypeId=gld.GradeLevelTypeId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId left join edfi.StudentSectionAssociation ssec on ssec.StudentUSI=s.StudentUSI left join edfi.Section sec on sec.SchoolId=ssec.SchoolId and sec.SchoolYear=ssec.SchoolYear and sec.ClassPeriodName=ssec.ClassPeriodName and sec.ClassroomIdentificationCode=ssec.ClassroomIdentificationCode and sec.LocalCourseCode=ssec.LocalCourseCode and sec.SequenceOfCourse=ssec.SequenceOfCourse and sec.TermDescriptorId=ssec.TermDescriptorId and sec.UniqueSectionCode=ssec.UniqueSectionCode left join edfi.CourseOffering co on co.LocalCourseCode=ssec.LocalCourseCode and co.SchoolId=ssec.SchoolId and co.SchoolYear=ssec.SchoolYear left join edfi.Course c on c.CourseCode=co.CourseCode left join edfi.StaffSectionAssociation stsec on stsec.SchoolId=sec.SchoolId and stsec.SchoolYear=sec.SchoolYear and stsec.ClassPeriodName=sec.ClassPeriodName and stsec.ClassroomIdentificationCode=sec.ClassroomIdentificationCode and stsec.LocalCourseCode=sec.LocalCourseCode and stsec.SequenceOfCourse=sec.SequenceOfCourse and stsec.TermDescriptorId=sec.TermDescriptorId and stsec.UniqueSectionCode=sec.UniqueSectionCode left join edfi.Staff sta on sta.StaffUSI=stsec.StaffUSI left join edfi.Descriptor dt on dt.DescriptorId=sec.TermDescriptorId left join edfi.StudentRace sr on sr.StudentUSI=s.StudentUSI left join edfi.RaceType rt on rt.RaceTypeId=sr.RaceTypeId where (ssa.SchoolYear is null or ssa.SchoolYear=2019) and ssa.StudentUSI is not null and ssec.StudentUSI is not null order by eo.NameOfInstitution,isnull(sta.LastSurname,''),isnull(sta.Firstname,''),c.CourseTitle+' T:'+isnull(dt.ShortDescription,'')+' P:'+isnull(sec.ClassPeriodName,''),s.LastSurname,s.FirstName