SELECT
RPAD( IF(LENGTH(p.id_number) = 13,COALESCE(p.id_number,''), COALESCE(null,'')),15,' ') as National_Id,
RPAD( IF(LENGTH(p.id_number) != 13,COALESCE(p.id_number,''), COALESCE(null,'')),20,' ') as Person_Alternate_Id,
RPAD(l_alt_id.che_lookup,3,' ') as Alternative_Id_Type,
RPAD(COALESCE(c.che_code,''),20,' ') as Qualification_Code,
RPAD(COALESCE(l_achieve_status.che_lookup,''),3,' ') Learner_Achievement_Status_Id,
RPAD(COALESCE(null,''),20,' ') as Assessor_Registration_Number,
RPAD(COALESCE(l_achieve_type.che_lookup,''),3,' ') Learner_Achievement_Type_Id,
RPAD(DATE_FORMAT(uc.achieved_at, "%Y%m%d"),8,' ') as Learner_Achievement_Date,

RPAD(DATE_FORMAT(first_r.enrolment_date, "%Y%m%d"),8,' ') Learner_Enrolled_Date,
RPAD(COALESCE(l_honors.che_lookup,''),3,' ') Honours_Classification,
RPAD('1',2, '') as Part_of,
RPAD(COALESCE(null,''),30,' ') as Learnership_Code,
RPAD(COALESCE('CHED-13',''),20,' ') as Provider_Code,
RPAD(COALESCE('621',''),10,' ') as Provider_Etqa_Id,
RPAD(COALESCE('71000',''),10,' ') as Assessor_Etqa_Id,
RPAD(COALESCE(l_cesm.che_lookup,''),10,' ') CESM1,
RPAD(COALESCE(null,''),10,' ') CESM2,
RPAD(DATE_FORMAT(last_r.enrolment_date, "%Y%m%d"),8,' ') Most_Recent_Enrolment_Date,
RPAD(DATE_FORMAT(last_r.updated_at, "%Y%m%d"),8,' ') Date_Stamp

FROM user_course uc
INNER JOIN registrations first_r ON first_r.course_id = uc.course_id AND first_r.user_id = uc.user_id AND first_r.deleted_at IS NULL
INNER JOIN registrations last_r ON last_r.course_id = uc.course_id AND last_r.user_id = uc.user_id AND last_r.deleted_at IS NULL

INNER JOIN users u ON uc.user_id = u.id
INNER JOIN profiles p ON u.id = p.user_id
INNER JOIN courses c on uc.course_id = c.id
LEFT JOIN lookups l_alt_id ON l_alt_id.id = p.identification_type_id
LEFT JOIN lookups l_achieve_status ON l_achieve_status.id = uc.status_id
LEFT JOIN lookups l_achieve_type ON l_achieve_type.id = uc.achievement_type_id
LEFT JOIN lookups l_honors ON l_honors.id = uc.honors_status_id
LEFT JOIN lookups l_cesm ON l_cesm.id = c.che_cesm_code_id
WHERE
uc.status_id IS NOT NULL
AND
first_r.id = (
      SELECT id
      FROM registrations rq
      WHERE rq.user_id = uc.user_id AND rq.course_id = uc.course_id AND rq.deleted_at IS NULL
      ORDER BY enrolment_date ASC LIMIT 1
   )
   AND
   last_r.id = (
      SELECT id
      FROM registrations rq
      WHERE rq.user_id = uc.user_id AND rq.course_id = uc.course_id AND rq.deleted_at IS NULL
      ORDER BY enrolment_date DESC LIMIT 1
   );