select s.StaffUSI,s.StaffUniqueId
,(select sic.IdentificationCode from edfi.StaffIdentificationCode sic 
left join edfi.Descriptor d on d.DescriptorId=sic.StaffIdentificationSystemDescriptorId
where d.Description='District' and sic.StaffUSI=s.StaffUSI) as DistrictStaffId
,(select sic.IdentificationCode from edfi.StaffIdentificationCode sic 
left join edfi.Descriptor d on d.DescriptorId=sic.StaffIdentificationSystemDescriptorId
where d.Description='State' and sic.StaffUSI=s.StaffUSI) as StateStaffId
,s.LastSurname,s.FirstName,s.MiddleName,eo.NameOfInstitution as SchoolAssignment,ssa.SchoolYear as SchoolYearAssigned,dp.Description as ProgramAssigned
,sea.EducationOrganizationId as EmployerId, eo2.NameOfInstitution as Employer, ds.Description as StaffClassification, sea.PositionTitle, sea.BeginDate, sea.EndDate
, sea.OrderOfAssignment
,(select sem.ElectronicMailAddress from edfi.StaffElectronicMail sem
left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId
where emt.Description='Work' and sem.StaffUSI=s.StaffUSI) as WorkEmail
,(select sem.ElectronicMailAddress from edfi.StaffElectronicMail sem
left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId
where emt.Description='Personal' and sem.StaffUSI=s.StaffUSI) as PersonalEmail
,s.LoginId
from edfi.Staff s
left join edfi.StaffSchoolAssociation ssa on ssa.StaffUSI=s.StaffUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
left join edfi.StaffEducationOrganizationAssignmentAssociation sea on sea.StaffUSI=s.StaffUSI
left join edfi.EducationOrganization eo2 on eo2.EducationOrganizationId=sea.EducationOrganizationId
left join edfi.Descriptor dp on dp.DescriptorId=ssa.ProgramAssignmentDescriptorId
left join edfi.Descriptor ds on ds.DescriptorId=sea.StaffClassificationDescriptorId
order by s.LastSurname,s.FirstName,eo.NameOfInstitution