SELECT
  U1.UserID,
  U1.UserName,
  IF((U1.Status = 1), 'Active', 'Cancelled') AS UserStatus,
  U1.FullName,
  BR.BranchName,
  U1.Office365Email,
  CONCAT(BG.UserBillingGroup, ' (R ', BG.Rate, ')') AS UserBillingGroup,
  UT.TravelRate,
  U1.JobTitle,
  U3.FullName AS TeamLeader,
  U4.UserRoles,
  (SELECT GROUP_CONCAT(X.BranchName SEPARATOR '<br>') FROM taBranches AS X WHERE (CONCAT(',', U1.AllowedRegions, ',') LIKE CONCAT('%,', X.BranchID, ',%'))) AS AllowedRegions,
  REPLACE(U1.AllowedProducts, ',', '<br>') AS AllowedProducts
FROM
  taUsers AS U1
    LEFT JOIN taUserBillingGroup AS BG ON (BG.UserBillingGroupID = U1.UserBillingGroupID)
    LEFT JOIN taUsersTravelRates AS UT ON (UT.UserID = U1.UserID)
    LEFT JOIN (
      SELECT
        UserID,
        fnUserBranch(UserID, @aEffectiveDate) AS BranchID
      FROM
        taUsers
    ) AS U2 ON (U2.UserID = U1.UserID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = U2.BranchID)
    LEFT JOIN taUsers AS U3 ON (U3.UserID = U1.UserID)
    LEFT JOIN (
      SELECT
        UserID,
        GROUP_CONCAT(SR.RoleDescription SEPARATOR '<br>') AS UserRoles
      FROM
        taUserRoleMember AS X
          INNER JOIN taSystemRole AS SR ON (SR.RoleName = X.RoleName)
      GROUP BY 1
    ) AS U4 ON (U4.UserID = U1.UserID)
WHERE
  (UT.TravelRateID = (

        SELECT MAX(X.TravelRateID) AS TravelRateID
        FROM taUsersTravelRates AS X
        WHERE
          (X.UserID = UT.UserID) AND
          (X.DateChanged = (
            SELECT MAX(Y.DateChanged)
            FROM taUsersTravelRates AS Y
            WHERE
              (Y.UserID = UT.UserID) AND
              (Y.DateChanged <= @aEffectiveDate)
          ))

  ))
  @pUserStatus
  @pBranchID
  @pConsultantID
  @pUserRoles
  @pAllowedRegions
  @pAllowedProducts
ORDER BY
  BR.BranchName,
  U1.FullName