select syt.SchoolYearDescription as SchoolYear,a.AssessmentFamily,dp.ShortDescription as Period,dc.ShortDescription as Category
, a.AssessmentTitle,s.LastSurname,s.FirstName,ds.ShortDescription as Subject
, dg.ShortDescription as GradeAssessed,a.AssessmentVersion,sa.AdministrationDate
, spl.PerformanceLevelMet,dpl.ShortDescription as PerformanceLevel,darm.CodeValue as ReportingMethod
from edfi.StudentAssessment sa
left join edfi.Student s on s.StudentUSI=sa.StudentUSI
left join edfi.AssessmentAcademicSubject aas on aas.AssessmentIdentifier=sa.AssessmentIdentifier and aas.Namespace=sa.Namespace
left join edfi.Descriptor ds on ds.DescriptorId=aas.AcademicSubjectDescriptorId
left join edfi.Descriptor dg on dg.DescriptorId=sa.WhenAssessedGradeLevelDescriptorId
left join edfi.Assessment a on a.AssessmentIdentifier=sa.AssessmentIdentifier and a.Namespace=sa.Namespace
left join edfi.AssessmentPeriod ap on ap.AssessmentIdentifier=a.AssessmentIdentifier and ap.Namespace=sa.Namespace
left join edfi.Descriptor dp on dp.DescriptorId=ap.AssessmentPeriodDescriptorId
left join edfi.Descriptor dc on dc.DescriptorId=a.AssessmentCategoryDescriptorId
left join edfi.StudentAssessmentPerformanceLevel spl on spl.StudentAssessmentIdentifier=sa.StudentAssessmentIdentifier and spl.StudentUSI=sa.StudentUSI and spl.AssessmentIdentifier=sa.AssessmentIdentifier and spl.Namespace=sa.Namespace
left join edfi.Descriptor dpl on dpl.DescriptorId=spl.PerformanceLevelDescriptorId
left join edfi.Descriptor darm on darm.DescriptorId=spl.AssessmentReportingMethodDescriptorId
left join edfi.SchoolYearType syt on syt.SchoolYear=sa.SchoolYear
order by s.LastSurname,s.FirstName,ds.ShortDescription,sa.AdministrationDate