select eo.NameOfInstitution,s.LastSurname,s.FirstName
, dsc.CodeValue as ServiceCoordCode, dsc.ShortDescription as ServiceCoordAgency
,dset.CodeValue as PrimaryServiceSettingCode, dset.ShortDescription as PrimaryServiceSetting
,ifsp.CurrentIFSPDate
,dtime.CodeValue as TimelyStartOfServiceCode, dtime.ShortDescription as TimelyStartOfService
,'Not functional' as PartCExitReason
,sps.ServiceEndDate as PartCExitDate
,dtran.CodeValue as TransitionIFSPCode, dtran.ShortDescription as TransitionIFSP
,dconf.CodeValue as TransitionConferenceCode, dconf.ShortDescription as TransitionConference
from mi.StudentEarlyLearningProgramAssociation spa
left join mi.StudentEarlyLearningProgramAssociationIndividualizedFamilyServicePlan ifsp on ifsp.StudentUSI=spa.StudentUSI and ifsp.BeginDate=spa.BeginDate
and ifsp.EducationOrganizationId=spa.EducationOrganizationId and ifsp.ProgramName=spa.ProgramName and ifsp.ProgramEducationOrganizationId=spa.ProgramEducationOrganizationId
and ifsp.ProgramTypeDescriptorId=spa.ProgramTypeDescriptorId
left join mi.StudentEarlyLearningProgramAssociationEarlyLearningProgramService sps on sps.StudentUSI=spa.StudentUSI and sps.BeginDate=spa.BeginDate
and sps.EducationOrganizationId=spa.EducationOrganizationId and sps.ProgramName=spa.ProgramName and sps.ProgramEducationOrganizationId=spa.ProgramEducationOrganizationId
and sps.ProgramTypeDescriptorId=spa.ProgramTypeDescriptorId
left join edfi.Descriptor dsc on dsc.DescriptorId=sps.ServiceCoordAgencyDescriptorId
left join edfi.Descriptor dset on dset.DescriptorId=sps.SettingDescriptorId
left join edfi.Descriptor dtime on dtime.DescriptorId=sps.TimelyStartOfServiceDescriptorId
left join edfi.Descriptor dtran on dtran.DescriptorId=ifsp.TransitionIFSPDescriptorId
left join edfi.Descriptor dconf on dconf.DescriptorId=ifsp.TransitionConferenceDescriptorId
left join edfi.Student s on s.StudentUSI=spa.StudentUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=spa.EducationOrganizationId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName