select eo.NameOfInstitution,s.LastSurname,s.FirstName,dg.CodeValue as GradeLevel
,p.ProgramName, pt.ShortDescription as ProgramType, spa.BeginDate, spa.EndDate as EndDate, 'EdOrg' as [Level]
from edfi.StudentEducationOrganizationAssociationProgramParticipation spa
left join edfi.Descriptor pt on pt.DescriptorId=spa.ProgramTypeDescriptorId 
left join edfi.Program p on p.ProgramTypeDescriptorId=spa.ProgramTypeDescriptorId and p.EducationOrganizationId=spa.EducationOrganizationId and p.ProgramTypeDescriptorId=spa.ProgramTypeDescriptorId
left join edfi.Student s on s.StudentUSI=spa.StudentUSI
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=s.StudentUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
left join edfi.Descriptor dg on dg.DescriptorId=ssa.EntryGradeLevelDescriptorId

union

select eo.NameOfInstitution,s.LastSurname,s.FirstName,dg.CodeValue as GradeLevel
,spa.ProgramName, pt.ShortDescription as ProgramType, spa.BeginDate, null as EndDate, 'School' as [Level]
from edfi.StudentProgramAssociation spa
left join edfi.Descriptor pt on pt.DescriptorId=spa.ProgramTypeDescriptorId 
left join edfi.Program p on p.ProgramTypeDescriptorId=spa.ProgramTypeDescriptorId and p.EducationOrganizationId=spa.ProgramEducationOrganizationId and p.ProgramName=spa.ProgramName
left join edfi.Student s on s.StudentUSI=spa.StudentUSI
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=s.StudentUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
left join edfi.Descriptor dg on dg.DescriptorId=ssa.EntryGradeLevelDescriptorId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName