SELECT
  X.ConsultantID,
  X.ConsultantName,
  X.ConsultantBranchID,
  CONVERT(fnConsultantProductCategory(X.ConsultantID) USING utf8) AS ConsultantProductCategory
FROM (

  SELECT
    BP.ConsultantID,
    U1.FullName AS ConsultantName,
    BR.BranchID AS ConsultantBranchID,
    SUM(COALESCE(fnClientBranchProductRetainer(BP.ClientBranchProductID, @aToDate), 0.0)) AS ConsultantRetainer
  FROM
    taClientBranchProducts AS BP
      LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
      LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
      LEFT JOIN taProducts       AS PR ON (PR.ProductID = CP.ProductID)
      LEFT JOIN taUsers          AS U1 ON (U1.UserID = BP.ConsultantID),
    taUserBranchMember AS BM
      LEFT JOIN taBranches AS BR ON (BR.BranchID = BM.BranchID)
  WHERE
    (BM.UserID = BP.ConsultantID) AND
    (BM.FromDate = (SELECT MAX(N.FromDate) FROM taUserBranchMember AS N WHERE (N.UserID = BP.ConsultantID) AND (N.FromDate <= @aToDate))) AND
    (U1.FullName IS NOT NULL) AND
    (CP.Status = 1) AND
    (BP.Status = 1) AND
    (BP.JoinDate <= @aToDate) AND
    ((BP.TerminationDate IS NULL) OR (BP.TerminationDate > @aToDate)) AND
    (PR.ProductCategory IN ('HEALTH AND SAFETY', 'HR', 'IR', 'PAYROLL', 'TRAINING')) AND
    (BP.ConsultantID > 0) AND
    (BR.BranchID NOT IN (18,116)) AND
    (CP.ProductID <> 14)

    @pBranchID
    @pConsultantID
    @pClientID

  GROUP BY 1,2

) AS X
WHERE
  (X.ConsultantRetainer > 0.0)
