SELECT
       GROUP_CONCAT(WP.Id),
       IF(UI.NationalIdentityNumber IS NULL OR UI.NationalIdentityNumber = '', '0',
          UI.NationalIdentityNumber)                                                                                    NationalIdentityNumber,
       CASE
           WHEN GROUP_CONCAT(DISTINCT WP.id) LIKE '%,%' THEN
                (SELECT WP2.id FROM WorkforcePersonal WP2 WHERE WP2.id IN (GROUP_CONCAT(WP.Id)) AND WP2.tenant = E.tenant LIMIT 1)
           ELSE GROUP_CONCAT(DISTINCT WP.id) END                                                                     AS WorkforcePersonalId,
       GROUP_CONCAT(DISTINCT UI.Tenant ORDER BY UI.Tenant DESC)                                                                              AS tenant,
       GROUP_CONCAT(DISTINCT UI.GivenName)                                                                           AS GivenName,
       GROUP_CONCAT(DISTINCT UI.FamilyName)                                                                          AS FamilyName,
       CASE
           WHEN GROUP_CONCAT(DISTINCT WP.id) LIKE '%,%' THEN
                TIMESTAMPDIFF(YEAR, (SELECT WP2.BirthDate FROM WorkforcePersonal WP2 WHERE WP2.id IN (GROUP_CONCAT(WP.Id)) AND WP2.tenant = E.tenant LIMIT 1), CURDATE())
           ELSE TIMESTAMPDIFF(YEAR, WP.BirthDate, CURDATE())  END                                                    AS age,
       GROUP_CONCAT(DISTINCT WP.Gender)                                                                              AS gender,
       CASE
           WHEN GROUP_CONCAT(DISTINCT WP.PreferredGivenName) IS NULL THEN CONCAT(GROUP_CONCAT(DISTINCT UI.GivenName),
                                                                                 ' ',
                                                                                 GROUP_CONCAT(DISTINCT UI.FamilyName))
           ELSE CONCAT(GROUP_CONCAT(DISTINCT WP.PreferredGivenName), ' ',
                       GROUP_CONCAT(DISTINCT UI.FamilyName)) END                                                     AS DisplayName,
       GROUP_CONCAT(DISTINCT WP.DisplayCode)                                                                         AS DisplayCode,
       GROUP_CONCAT(DISTINCT UI.Email)                                                                               AS Email,
       GROUP_CONCAT(DISTINCT A.AddressLine1)                                                                         AS AddressLine1,
       GROUP_CONCAT(DISTINCT A.AddressLine2)                                                                         AS AddressLine2,
       GROUP_CONCAT(DISTINCT A.AddressLine3)                                                                         AS AddressLine3,
       GROUP_CONCAT(DISTINCT A.AddressLine4)                                                                         AS AddressLine4,
       GROUP_CONCAT(DISTINCT WP.HomePhone)                                                                           AS HomePhone,
       CASE
           WHEN GROUP_CONCAT(DISTINCT WP.FullTimeEquivalentAvailable) LIKE '%,%' OR NULL THEN 0
           ELSE GROUP_CONCAT(DISTINCT WP.FullTimeEquivalentAvailable) END                                            AS FullTimeEquivalentAvailable,
       CASE
           WHEN GROUP_CONCAT(DISTINCT WP.BirthDate) LIKE '%,%' THEN
                (SELECT WP2.BirthDate FROM WorkforcePersonal WP2 WHERE WP2.id IN (GROUP_CONCAT(WP.Id)) AND WP2.tenant = E.tenant LIMIT 1)
           ELSE DATE(GROUP_CONCAT(DISTINCT WP.BirthDate)) END                                                        AS BirthDate,
       CASE
           WHEN GROUP_CONCAT(DISTINCT WP.Invigilator) LIKE '%,%' THEN
                (SELECT WP2.Invigilator FROM WorkforcePersonal WP2 WHERE WP2.id IN (GROUP_CONCAT(WP.Id)) AND WP2.tenant = E.tenant LIMIT 1)
           ELSE GROUP_CONCAT(DISTINCT WP.Invigilator) END                                                            AS Invigilator,
       CASE
           WHEN GROUP_CONCAT(DISTINCT WP.Examiner) LIKE '%,%' THEN
                (SELECT WP2.Examiner FROM WorkforcePersonal WP2 WHERE WP2.id IN (GROUP_CONCAT(WP.Id)) AND WP2.tenant = E.tenant LIMIT 1)
           ELSE GROUP_CONCAT(DISTINCT WP.Examiner) END                                                               AS Examiner,
       GROUP_CONCAT(DISTINCT E.Department)                                                                           AS Department,
       GROUP_CONCAT(DISTINCT WP.LeftSchool)                                                                          AS LeftSchool,
       GROUP_CONCAT(DISTINCT SIMAIN.SchoolName)                                                                      AS MainSchools,
       GROUP_CONCAT(DISTINCT UI.UserRole)                                                                            AS role,
       GROUP_CONCAT(DISTINCT SI.SchoolName)                                                                          AS Schools,
       MAX(E.jobTitle) AS jobTitle,
       MAX(E.positionCode) AS positionCode,
       MAX(E.seniority) AS seniority,
       MAX(E.startDate) AS startDate,
       MAX(E.endDate) AS endDate,
       (CASE
            WHEN E.includeInTimetabling IS NULL THEN 'Y'
            WHEN E.includeInTimetabling = 'YES' THEN 'Y'
            ELSE 'N' END)                                                                                            AS includeInTimetabling,
       MAX(E.mainJob) AS mainJob,
       MAX(E.tenant)                                                                                                      AS employmentTenant,
       SUSN.SubNumber                                                                                               AS smsSubNumber
FROM WorkforcePersonal WP
         JOIN UserInfo UI ON UI.Id = WP.UserInfoId AND UI.UserRole != 'ROLE_LEARNER'
         LEFT JOIN (SELECT E.*
                    FROM Employment E
                             INNER JOIN (SELECT ordered.id, ordered.tenant, ordered.NationalIdentityNumber
                                         FROM (SELECT id, tenant, NationalIdentityNumber, mainJob, active
                                               FROM Employment
                                               GROUP BY tenant, NationalIdentityNumber, mainJob, active
                                               HAVING CASE
                                                          WHEN mainJob = 1 THEN mainJob = 1
                                                          ELSE (COUNT(1) = 1 OR (COUNT(1) > 1 AND ACTIVE = 1)) END
                                               ORDER BY Tenant DESC, MainJob DESC, Active DESC) ordered
                                         GROUP BY ordered.tenant, ordered.NationalIdentityNumber) filtered
                                        ON filtered.id = E.id) E
                   ON E.nationalIdentityNumber = UI.NationalIdentityNumber AND E.Tenant = UI.Tenant
         LEFT JOIN UserAssociatedSchool UAS ON UAS.userInfoId = UI.Id AND UAS.dateTo IS NULL
         LEFT JOIN UserAssociatedSchool UASMAIN
                   ON UASMAIN.userInfoId = UI.Id AND UASMAIN.mainSchool = TRUE AND UASMAIN.dateTo IS NULL
         LEFT JOIN SchoolInfo SI ON SI.Tenant = UAS.Tenant
         LEFT JOIN SchoolInfo SIMAIN ON SIMAIN.Tenant = UASMAIN.Tenant
         LEFT JOIN WorkforcePersonal_Address WPA ON WPA.WorkforcePersonal_Id = WP.Id AND WPA.Tenant = WP.Tenant AND
                                                    (WPA.addressList_Id = (SELECT MAX(WPA2.addressList_Id)
                                                                           FROM WorkforcePersonal_Address WPA2
                                                                           WHERE WPA2.WorkforcePersonal_Id = WP.Id
                                                                             AND WPA2.Tenant = WP.Tenant))
         LEFT JOIN Address A ON A.Id = WPA.addressList_Id
         LEFT JOIN SmsUserSubNumber SUSN ON UI.Tenant = SUSN.Tenant AND UI.Id = SUSN.UserInfoId
WHERE WP.Tenant IN (12612098, 2041)
  AND 1 = 1
  AND WP.LeftSchool <> 'Y'
  AND UI.UserRole IN ('ROLE_PRINCIPAL', 'ROLE_TEACHER', 'ROLE_BASIC', 'ROLE_EXTERNALS_ADMIN')
GROUP BY IF(UI.NationalIdentityNumber IS NULL OR UI.NationalIdentityNumber = '', UI.Id, UI.NationalIdentityNumber)
ORDER BY CONCAT(UI.FamilyName, UI.GivenName) ASC;