SELECT * FROM (

  SELECT
    U1.UserID,
    U1.Office365Email,
    U1.FullName,
    COALESCE(U1.MapReference, '') AS MapReference,
    IF(U1.UserID = @nConsultantID, 1, 0) AS IsSelectedUser,
    IF(COALESCE(X.AttendanceCount, 0) < 5, 0, COALESCE(X.AttendanceCount, 0)) AS ClientAttendanceCount,
    IF(COALESCE(Y.AttendanceCount, 0) < 5, 0, COALESCE(Y.AttendanceCount, 0)) AS BookedByAttendanceCount,
    IF(COALESCE(Z.AttendanceCount, 0) < 5, 0, COALESCE(Z.AttendanceCount, 0)) AS ProductAttendanceCount
  FROM
    taUsers AS U1
      LEFT JOIN (

        -- Client Branch.
        SELECT
          AP.ConsultantID AS UserID,
          COUNT(*) AS AttendanceCount
        FROM
          tbAppointments AS AP
            INNER JOIN taProducts AS PR USING (ProductID)
        WHERE
          (AP.ClientBranchID = @nClientBranchID) AND
          (AP.AttendanceDate >= DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND
          (AP.AppointmentStage = 'Authorised') AND
          (PR.ProductCategory IN (SELECT ProductCategory FROM taProducts WHERE (ProductID = @nProductID)))
        GROUP BY
          ConsultantID
      ) AS X USING (UserID)

      -- Booked By.
      LEFT JOIN (
        SELECT
          AP.ConsultantID AS UserID,
          COUNT(*) AS AttendanceCount
        FROM
          tbAppointments AS AP
            INNER JOIN taProducts AS PR USING (ProductID)
        WHERE
          (AP.BookedByID = @nBookedByID) AND
          (AP.AttendanceDate >= DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND
          (AP.AppointmentStage = 'Authorised') AND
          (PR.ProductCategory IN (SELECT ProductCategory FROM taProducts WHERE (ProductID = @nProductID)))
        GROUP BY
          ConsultantID
      ) AS Y USING (UserID)

      -- Product Region.
      LEFT JOIN (
        SELECT
          AP.ConsultantID AS UserID,
          COUNT(*) AS AttendanceCount
        FROM
          tbAppointments AS AP
            INNER JOIN taProducts AS PR USING (ProductID)
            INNER JOIN taClientBranches AS CB USING (ClientBranchID)
        WHERE
          (AP.AttendanceDate >= DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND
          (AP.AppointmentStage = 'Authorised') AND
          (CB.LabourNetBranchID = (SELECT LabourNetBranchID FROM taClientBranches WHERE (ClientBranchID = @nClientBranchID))) AND
          (PR.ProductCategory IN (SELECT ProductCategory FROM taProducts WHERE (ProductID = @nProductID)))
        GROUP BY
          ConsultantID
      ) AS Z USING (UserID)
  WHERE
    (U1.Status = 1) AND
    (U1.IsResource = 0)

) AS Z
WHERE
  (Z.UserID = @nConsultantID) OR
  (Z.ClientAttendanceCount > 0) OR
  (Z.BookedByAttendanceCount > 0) OR
  (Z.ProductAttendanceCount > 0)
ORDER BY
  5 DESC,
  6 DESC,
  7 DESC,
  8 DESC
LIMIT @nMaxCount