select distinct eic.IdentificationCode 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.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId 
	where sem.StaffUSI=sta.StaffUSI and emt.ShortDescription='Work'),'') as [User Name]
,isnull((select sem.ElectronicMailAddress from edfi.StaffElectronicMail sem left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId 
	where sem.StaffUSI=sta.StaffUSI and emt.ShortDescription='Work'),'') as [Email Address]
,c.CourseTitle+' T:'+isnull(dt.ShortDescription,'')+' P:'+isnull(sec.SectionIdentifier,'') as [Class Name]
,'' as [Previous Student ID]
,(select sic.IdentificationCode from edfi.StudentEducationOrganizationAssociationStudentIdentificationCode 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.StudentEducationOrganizationAssociationStudentIdentificationCode 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 gld.CodeValue
	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 'Preschool/Prekindergarten' then '30'
	when 'Postsecondary' then '20'
	when 'Ungraded' then '13'
	when 'No grade level' then '13'
	when 'Other' then '13'
	when 'Grade 13' then '13'
	else isnull(gld.CodeValue,'  ') end
	as [Student Grade]
, case when (select count(*) from edfi.StudentEducationOrganizationAssociationRace 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.StudentEducationOrganizationAssociationElectronicMail sem left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId 
	where sem.StudentUSI=s.StudentUSI and emt.ShortDescription='Organization'),'') as [Student User Name]
,isnull((select sem.ElectronicMailAddress from edfi.StudentEducationOrganizationAssociationElectronicMail sem left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId 
	where sem.StudentUSI=s.StudentUSI and emt.ShortDescription='Organization'),'') as [Student Email]
from edfi.Student s 
left join edfi.StudentEducationOrganizationAssociation seoa on seoa.StudentUSI=s.StudentUSI
left join edfi.Descriptor st on st.DescriptorId=seoa.SexDescriptorId
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=s.StudentUSI
left join edfi.Descriptor gld on gld.DescriptorId=ssa.EntryGradeLevelDescriptorId
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
left join edfi.EducationOrganizationIdentificationCode eic on eic.EducationOrganizationId=eo.EducationOrganizationId
left join edfi.Descriptor deo on deo.DescriptorId=eic.EducationOrganizationIdentificationSystemDescriptorId
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.LocalCourseCode=ssec.LocalCourseCode and sec.SectionIdentifier=ssec.SectionIdentifier and sec.SessionName=ssec.SessionName
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.LocalCourseCode=sec.LocalCourseCode 
	and stsec.SectionIdentifier=sec.SectionIdentifier and stsec.SessionName=sec.SessionName 
left join edfi.Staff sta on sta.StaffUSI=stsec.StaffUSI
left join edfi.[Session] sess on sess.SessionName = sec.SessionName and sess.SchoolId=sec.SchoolId and sess.SchoolYear=sec.SchoolYear
left join edfi.Descriptor dt on dt.DescriptorId=sess.TermDescriptorId
left join edfi.StudentEducationOrganizationAssociationRace sr on sr.StudentUSI=s.StudentUSI
left join edfi.Descriptor rt on rt.DescriptorId=sr.RaceDescriptorId
where (ssa.SchoolYear is null or ssa.SchoolYear=(select sy.SchoolYear from edfi.SchoolYearType sy where sy.CurrentSchoolYear=1)) 
	and ssa.StudentUSI is not null and ssec.StudentUSI is not null
	and deo.CodeValue='SEA'
order by eo.NameOfInstitution,isnull(sta.LastSurname,''),isnull(sta.Firstname,''),c.CourseTitle+' T:'+isnull(dt.ShortDescription,'')+' P:'+isnull(sec.SectionIdentifier,''),s.LastSurname,s.FirstName