SELECT isnull(eo.NameOfInstitution,eo.ShortNameOfInstitution) as School ,s.LastSurname, s.FirstName, isnull(s.MiddleName,'') as MiddleName ,s.StudentUniqueId, dgl.CodeValue as GradeLevel, e.EventDate ,isnull(e.AttendanceEventReason,'') reason, d.ShortDescription AttCategory ,aect.Description as AttType, dt.ShortDescription term, dc.Description as DayDescription ,coalesce(co.LocalCourseTitle,c.CourseTitle) as CourseTitle,co.CourseCode,e.LocalCourseCode,e.ClassPeriodName,e.ClassroomIdentificationCode FROM edfi.studentsectionattendanceevent e left join edfi.CourseOffering co on co.LocalCourseCode=e.LocalCourseCode and co.SchoolId=e.SchoolId and co.SchoolYear=e.SchoolYear and co.TermDescriptorId=e.TermDescriptorId left JOIN edfi.student s ON s.StudentUSI = e.StudentUSI left JOIN edfi.EducationOrganization eo ON eo.EducationOrganizationId = e.SchoolId left JOIN edfi.Descriptor d ON d.DescriptorId = e.AttendanceEventCategoryDescriptorId left JOIN edfi.Descriptor dt ON dt.DescriptorId = e.TermDescriptorId left join edfi.CalendarDateCalendarEvent cd on cd.Date=e.EventDate and cd.SchoolId=e.SchoolId left join edfi.Descriptor dc on dc.DescriptorId=cd.CalendarEventDescriptorId left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=e.StudentUSI and ssa.SchoolId=e.SchoolId and ssa.SchoolYear=e.SchoolYear left join edfi.Descriptor dgl on dgl.DescriptorId=ssa.EntryGradeLevelDescriptorId left join edfi.AttendanceEventCategoryDescriptor aecd on aecd.AttendanceEventCategoryDescriptorId=e.AttendanceEventCategoryDescriptorId left join edfi.AttendanceEventCategoryType aect on aect.AttendanceEventCategoryTypeId=aecd.AttendanceEventCategoryTypeId left join edfi.Course c on c.CourseCode=co.CourseCode order by eo.NameOfInstitution,s.LastSurname,s.FirstName,e.EventDate