select a.AssessmentFamilyTitle,dp1.ShortDescription as Period,dc.ShortDescription as Category
, sa.AssessmentTitle,s.LastSurname,s.FirstName,ds.ShortDescription as Subject,dg.ShortDescription as GradeAssessed,sa.Version,sa.AdministrationDate
, oa.Description as Objective, soap.PerformanceLevelMet as PLMet,dp.ShortDescription as PL
from edfi.StudentAssessment sa
left join edfi.Student s on s.StudentUSI=sa.StudentUSI
left join edfi.Descriptor ds on ds.DescriptorId=sa.AcademicSubjectDescriptorId
left join edfi.Descriptor dg on dg.DescriptorId=sa.AssessedGradeLevelDescriptorId
left join edfi.StudentAssessmentStudentObjectiveAssessmentPerformanceLevel soap on soap.StudentUSI=sa.StudentUSI and soap.AssessmentTitle=sa.AssessmentTitle and soap.AcademicSubjectDescriptorId=sa.AcademicSubjectDescriptorId and soap.AssessedGradeLevelDescriptorId=sa.AssessedGradeLevelDescriptorId and soap.Version=sa.Version and soap.AdministrationDate=sa.AdministrationDate
left join edfi.ObjectiveAssessment oa on oa.AssessmentTitle=soap.AssessmentTitle and oa.AcademicSubjectDescriptorId=soap.AcademicSubjectDescriptorId and oa.AssessedGradeLevelDescriptorId=soap.AssessedGradeLevelDescriptorId and oa.Version=sa.Version and oa.IdentificationCode=soap.IdentificationCode
left join edfi.Descriptor dp on dp.DescriptorId=soap.PerformanceLevelDescriptorId
left join edfi.Assessment a on a.AssessmentTitle=sa.AssessmentTitle and a.AssessedGradeLevelDescriptorId=sa.AssessedGradeLevelDescriptorId and a.Version=sa.Version and a.AcademicSubjectDescriptorId=sa.AcademicSubjectDescriptorId
left join edfi.Descriptor dp1 on dp1.DescriptorId=a.AssessmentPeriodDescriptorId
left join edfi.Descriptor dc on dc.DescriptorId=a.AssessmentCategoryDescriptorId
order by s.LastSurname,s.FirstName,ds.ShortDescription,oa.Description,sa.AdministrationDate