SELECT
  Y.ProductCategory,
  Y.LabourNetBranch,
  Y.ClientID,
  Y.FinNumber,
  Y.GroupName,
  SUM(Y.BranchProductRetainer) AS BranchRetainer
FROM (

  SELECT
    X.*,
    fnClientBranchProductRetainer(X.ClientBranchProductID, NOW()) * 1.0 AS BranchProductRetainer
  FROM (

    SELECT
      PR.ProductCategory,
      BR.BranchName AS LabourNetBranch,
      CB.ClientID,
      CS.FinNumber,
      LEFT(REPLACE(REPLACE(CS.GroupName, ',', ''), '''', ''), 40) AS GroupName,
      BP.ClientBranchProductID,
      CB.ClientBranchID,
      BP.ClientProductID,
      BP.ConsultantID
    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 taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
        LEFT JOIN taClients AS CS ON (CS.ClientID = CB.ClientID)
        LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    WHERE
      (CS.ClientStatus = 1) AND
      (CB.BranchStatus = 1) AND
      (CP.Status = 1) AND
      (BP.Status = 1) AND
      (BR.BranchName IS NOT NULL)
      @pBranchID
      @pConsultantID

  ) AS X

) AS Y
WHERE
  (Y.BranchProductRetainer > 0)
GROUP BY
  Y.ProductCategory,
  Y.LabourNetBranch,
  Y.ClientID
