select eo.NameOfInstitution,s.LastSurname,s.FirstName, dgp.CodeValue as GradingPeriodDescriptor, g.BeginDate, gt.Description as GradeType
, c.CourseTitle, sec.LocationClassroomIdentificationCode, dt.CodeValue as Term, g.BeginDate, g.LetterGradeEarned, g.NumericGradeEarned, g.DiagnosticStatement
, pbt.Description as PerformanceBaseConversionType, st.LastSurname as TeacherLast, st.FirstName as TeacherFirst
, das.CodeValue as SubjectCode,das.Description as Subject
from edfi.Grade g
left join edfi.Student s on s.StudentUSI=g.StudentUSI
left join edfi.Descriptor dgp on dgp.DescriptorId=g.GradingPeriodDescriptorId
left join edfi.Descriptor gt on gt.DescriptorId=g.GradeTypeDescriptorId
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=g.SchoolId
left join edfi.Section sec on sec.SchoolId=g.SchoolId and sec.SchoolYear=g.SchoolYear 
	and sec.SectionIdentifier=g.SectionIdentifier and sec.SessionName=g.SessionName and sec.LocalCourseCode=g.LocalCourseCode
left join edfi.Session sess on sess.SessionName=sec.SessionName and sess.SchoolId=sec.SchoolId and sess.SchoolYear=sec.SchoolYear
left join edfi.Descriptor dt on dt.DescriptorId=sess.TermDescriptorId
left join edfi.CourseOffering co on co.LocalCourseCode=g.LocalCourseCode and co.SchoolId=g.SchoolId and co.SchoolYear=g.SchoolYear
left join edfi.Course c on c.CourseCode=co.CourseCode and c.EducationOrganizationId=co.EducationOrganizationId
left join edfi.Descriptor pbt on pbt.DescriptorId=g.PerformanceBaseConversionDescriptorId
left join edfi.StaffSectionAssociation ssa on ssa.SchoolId=g.SchoolId and ssa.SchoolYear=g.SchoolYear and ssa.SectionIdentifier=g.SectionIdentifier and ssa.SessionName=g.SessionName
       and ssa.LocalCourseCode=g.LocalCourseCode
left join edfi.Staff st on st.StaffUSI=ssa.StaffUSI
left join edfi.Descriptor das on das.DescriptorId=c.AcademicSubjectDescriptorId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName,dt.DescriptorId,gt.DescriptorId