select 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
, oa.Description as Objective, soap.Result, rt.ShortDescription as ResultDatatype, armt.ShortDescription as AssessmentReportingMethod
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.StudentAssessmentStudentObjectiveAssessmentScoreResult soap on soap.StudentUSI=sa.StudentUSI 
       and soap.StudentAssessmentIdentifier=sa.StudentAssessmentIdentifier 
       and soap.AssessmentIdentifier=sa.AssessmentIdentifier
       and soap.Namespace = sa.Namespace
left join edfi.ObjectiveAssessment oa on oa.AssessmentIdentifier=soap.AssessmentIdentifier 
       and oa.Namespace=soap.Namespace 
       and oa.IdentificationCode=soap.IdentificationCode
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.Descriptor rt on rt.DescriptorId=soap.ResultDatatypeTypeDescriptorId
left join edfi.Descriptor armt on armt.DescriptorId=soap.AssessmentReportingMethodDescriptorId
order by s.LastSurname,s.FirstName,ds.ShortDescription,oa.Description,sa.AdministrationDate