select a.AssessmentFamilyTitle,dp.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
, dt.ShortDescription as ReportingType, sas.Result, rt.ShortDescription as ResultType
, spl.PerformanceLevelMet,dpl.ShortDescription as PerformanceLevel
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.StudentAssessmentScoreResult sas on sas.StudentUSI=sa.StudentUSI and sas.AssessmentTitle=sa.AssessmentTitle and sas.AcademicSubjectDescriptorId=sa.AcademicSubjectDescriptorId and sas.AssessedGradeLevelDescriptorId=sa.AssessedGradeLevelDescriptorId and sas.Version=sa.Version and sas.AdministrationDate=sa.AdministrationDate
left join edfi.AssessmentReportingMethodType dt on dt.AssessmentReportingMethodTypeId=sas.AssessmentReportingMethodTypeId
left join edfi.ResultDatatypeType rt on rt.ResultDatatypeTypeId=sas.ResultDatatypeTypeId
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 dp on dp.DescriptorId=a.AssessmentPeriodDescriptorId
left join edfi.Descriptor dc on dc.DescriptorId=a.AssessmentCategoryDescriptorId
left join edfi.StudentAssessmentPerformanceLevel spl on spl.AssessmentTitle=sa.AssessmentTitle and spl.AssessedGradeLevelDescriptorId=sa.AssessedGradeLevelDescriptorId
and spl.AcademicSubjectDescriptorId=sa.AcademicSubjectDescriptorId and spl.[Version]=sa.[Version] and spl.AdministrationDate=sa.AdministrationDate
and spl.StudentUSI=sa.StudentUSI
left join edfi.Descriptor dpl on dpl.DescriptorId=spl.PerformanceLevelDescriptorId
order by s.LastSurname,s.FirstName,ds.ShortDescription,rt.ShortDescription,sa.AdministrationDate