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
,aecd.Description as AttType, dt.ShortDescription term, dc.Description as DayDescription
,coalesce(co.LocalCourseTitle,c.CourseTitle) as CourseTitle,co.CourseCode,e.LocalCourseCode,e.SectionIdentifier,e.SessionName,sec.LocationClassroomIdentificationCode as Classroom
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.SessionName=e.SessionName
left join edfi.Session sess on sess.SessionName=co.SessionName and sess.SchoolId=co.SchoolId and sess.SchoolYear=co.SchoolYear
left join edfi.Section sec on sec.SchoolId=e.SchoolId and sec.SchoolYear=e.SchoolId and sec.SectionIdentifier=e.SectionIdentifier and sec.LocalCourseCode=e.LocalCourseCode
	and sec.SessionName=e.SessionName
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 = sess.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.Descriptor aecd on aecd.DescriptorId=e.AttendanceEventCategoryDescriptorId
left join edfi.Course c on c.CourseCode=co.CourseCode
order by eo.NameOfInstitution,s.LastSurname,s.FirstName,e.EventDate