SELECT
  U1.UserID,
  TRIM(CONCAT(U1.FullName, ' ', IF(U1.Status = 0, '(cancelled)', ''))) AS FullName
FROM
  taUsers AS U1
    LEFT JOIN taUserRoleMember AS X ON (X.UserID = U1.UserID)
    LEFT JOIN (SELECT audKey, audDate FROM (
      (SELECT A.audKey, A.audDate FROM tbAudit AS A
       WHERE (A.audTable = 'taUsers') AND (A.audField = 'Status') AND (A.audPre = 1)
          AND (A.audPost = 0) AND (A.audDate > DATE_SUB(NOW(), INTERVAL 45 DAY)))
      UNION
      (SELECT B.audKey, B.audDate FROM cms_archive.tbAudit AS B
       WHERE (B.audTable = 'taUsers') AND (B.audField = 'Status') AND (B.audPre = 1)
          AND (B.audPost = 0) AND (B.audDate > DATE_SUB(NOW(), INTERVAL 45 DAY)))
    ) AS M) AS N ON (N.audKey = U1.UserID)
WHERE
  ((@aRoleName = '%%') OR (X.RoleName LIKE '%%')) AND
  ((U1.Status = 1) OR (N.audDate IS NOT NULL)) AND
  (U1.IsResource = 0)
  @pBranchID
ORDER BY
  U1.FullName

