SELECT
'StudentSectionAttendanceEvent' AS EdFiAttendanceType,
Student.FirstName AS StudentFirstName,
Student.MiddleName AS StudentMiddleName,
Student.LastSurname AS StudentLastSurname,
Student.BirthDate AS StudentBirthDate,
Student.StudentUniqueId as StudentUniqueId,
StudentSectionAttendanceEvent.SchoolId AS SchoolID,
StudentSectionAttendanceEvent.SchoolYear AS SchoolYear,
StudentSectionAttendanceEventTermDescriptor.CodeValue AS AttendanceEventTermDescriptor,
StudentSectionAttendanceEvent.EventDate AS AttendanceEventDate,
StudentSectionAttendanceEventAttendanceEventCategoryDescriptor.CodeValue AS AttendanceCategoryDescriptor,
StudentSectionAttendanceEvent.AttendanceEventReason AS AttendanceReason,
StudentSectionAttendanceEvent.LocalCourseCode AS LocalCourseCode,
StudentSectionAttendanceEvent.ClassroomIdentificationCode AS ClassroomIdentificationCode,
StudentSectionAttendanceEvent.ClassPeriodName AS ClassPeriodName,          
StudentSectionAttendanceEvent.UniqueSectionCode AS UniqueSectionCode

FROM edfi.Student
LEFT JOIN edfi.StudentSectionAttendanceEvent AS [StudentSectionAttendanceEvent]
  ON StudentSectionAttendanceEvent.StudentUSI = Student.StudentUSI
LEFT JOIN edfi.Descriptor AS [StudentSectionAttendanceEventAttendanceEventCategoryDescriptor]
  ON StudentSectionAttendanceEvent.AttendanceEventCategoryDescriptorId = StudentSectionAttendanceEventAttendanceEventCategoryDescriptor.DescriptorId
LEFT JOIN edfi.EducationalEnvironmentType AS [StudentSectionAttendanceEventEducationalEnvironmentType]
  ON StudentSectionAttendanceEvent.EducationalEnvironmentTypeId = StudentSectionAttendanceEventEducationalEnvironmentType.EducationalEnvironmentTypeId
LEFT JOIN edfi.Descriptor AS [StudentSectionAttendanceEventTermDescriptor]
  ON StudentSectionAttendanceEvent.TermDescriptorId = StudentSectionAttendanceEventTermDescriptor.DescriptorId

UNION ALL

SELECT 
'StudentSchoolAttendanceEvent' AS EdFiAttendanceType,
Student.FirstName AS StudentFirstName,
Student.MiddleName AS StudentMiddleName,
Student.LastSurname AS StudentLastSurname,
Student.BirthDate AS StudentBirthDate,
Student.StudentUniqueId as StudentUniqueId,
StudentSchoolAttendanceEvent.SchoolId AS SchoolID,
StudentSchoolAttendanceEvent.SchoolYear AS SchoolYear,
StudentSchoolAttendanceEventTermDescriptor.CodeValue AS AttendanceEventTermDescriptor,
StudentSchoolAttendanceEvent.EventDate AS AttendanceEventDate,
StudentSchoolAttendanceEventAttendanceEventCategoryDescriptor.CodeValue AS AttendanceCategoryDescriptor,
StudentSchoolAttendanceEvent.AttendanceEventReason AS AttendanceReason,
'' AS LocalCourseCode,
'' AS ClassroomIdentificationCode,
'' AS ClassPeriodName,          
'' AS UniqueSectionCode

FROM edfi.Student
LEFT JOIN edfi.StudentSchoolAttendanceEvent
  ON StudentSchoolAttendanceEvent.StudentUSI = Student.StudentUSI

LEFT JOIN edfi.Descriptor AS [StudentSchoolAttendanceEventAttendanceEventCategoryDescriptor]
  ON StudentSchoolAttendanceEvent.AttendanceEventCategoryDescriptorId = StudentSchoolAttendanceEventAttendanceEventCategoryDescriptor.DescriptorId
LEFT JOIN edfi.Descriptor AS [StudentSchoolAttendanceEventTermDescriptor]
  ON StudentSchoolAttendanceEvent.TermDescriptorId = StudentSchoolAttendanceEventTermDescriptor.DescriptorId