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
, dt.ShortDescription as ReportingType, sas.Result, rt.ShortDescription as ResultType
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.StudentAssessmentScoreResult sas on sas.StudentUSI=sa.StudentUSI 
       and sas.StudentAssessmentIdentifier=sa.StudentAssessmentIdentifier 
       and sas.Namespace=sa.Namespace 
       and sas.AssessmentIdentifier=sa.AssessmentIdentifier
left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId
left join edfi.Descriptor rt on rt.DescriptorId=sas.ResultDatatypeTypeDescriptorId
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.SchoolYearType syt on syt.SchoolYear=sa.SchoolYear
order by s.LastSurname,s.FirstName,ds.ShortDescription,rt.ShortDescription,sa.AdministrationDate