select eo.NameOfInstitution,s.LastSurname,s.FirstName, car.Description as CourseAttemptResultType, ct.CourseEducationOrganizationId, sy.SchoolYearDescription
, dt.CodeValue as Term, ct.CourseCode, c.CourseTitle, ct.AttemptedCredits, acrt.Description as AttemptedCreditType, ct.AttemptedCreditConversion
, ct.EarnedCredits, ecrt.Description as EarnedCreditType, ct.EarnedCreditConversion, dg.CodeValue as GradeTaken, mct.Description as MethodCreditEarned
, ct.FinalLetterGradeEarned, ct.FinalNumericGradeEarned, crct.Description as CourseRepeatCode, ct.CourseTitle as CourseTitleOther, ct.AlternativeCourseCode, ct.AlternativeCourseTitle
, das.CodeValue as SubjectCode,das.Description as Subject
from edfi.CourseTranscript ct
left join edfi.Student s on s.StudentUSI=ct.StudentUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ct.SchoolId
left join edfi.CourseAttemptResultType car on car.CourseAttemptResultTypeId=ct.CourseAttemptResultTypeId
left join edfi.SchoolYearType sy on sy.SchoolYear=ct.SchoolYear
left join edfi.Course c on c.CourseCode=ct.CourseCode and c.EducationOrganizationId=ct.EducationOrganizationId 
left join edfi.Descriptor dt on dt.DescriptorId=ct.TermDescriptorId
left join edfi.CreditType acrt on acrt.CreditTypeId=ct.AttemptedCreditTypeId
left join edfi.CreditType ecrt on ecrt.CreditTypeId=ct.EarnedCreditTypeId
left join edfi.Descriptor dg on dg.DescriptorId=ct.WhenTakenGradeLevelDescriptorId
left join edfi.MethodCreditEarnedType mct on mct.MethodCreditEarnedTypeId=ct.MethodCreditEarnedTypeId
left join edfi.CourseRepeatCodeType crct on crct.CourseRepeatCodeTypeId=ct.CourseRepeatCodeTypeId
left join edfi.Descriptor das on das.DescriptorId=c.AcademicSubjectDescriptorId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName,ct.SchoolYear,ct.TermDescriptorId, coalesce(c.CourseTitle,ct.CourseTitle)