SELECT
  Y.ClientID,
  Y.FinNumber,
  Y.GroupName,
  @pProductColumns
  SUM(Y.Retainer) AS Retainer
FROM (

  SELECT
    BP.ClientBranchProductID,
    IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.ClientID, CS.ParentClientID), CS.ClientID) AS ClientID,
    IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.FinNumber, CSP.FinNumber), CS.FinNumber) AS FinNumber,
    IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.GroupName, CSP.GroupName), CS.GroupName) AS GroupName,
    PR.ProductCategory,
    COALESCE(RF.RetainerFee, 0.0) AS Retainer
  FROM
    taClientBranchProducts AS BP
      LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
      LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
      LEFT JOIN taClients        AS CS ON (CS.ClientID = CP.ClientID)
      LEFT JOIN taProducts       AS PR ON (PR.ProductID = CP.ProductID)
      LEFT JOIN taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
      LEFT JOIN taClients        AS CSP ON (CSP.ClientID = CS.ParentClientID)
      LEFT JOIN (
        SELECT
          PF.ClientBranchProductID,
          PF.RetainerFee
        FROM
          taClientProductFees AS PF
        WHERE
          (PF.ClientProductFeeID = (
            SELECT MAX(M.ClientProductFeeID)
            FROM taClientProductFees AS M
            WHERE
              (M.ClientBranchProductID = PF.ClientBranchProductID) AND
              (M.StartDate = (
                SELECT MAX(N.StartDate)
                FROM taClientProductFees AS N
                WHERE
                  (N.ClientBranchProductID = M.ClientBranchProductID) AND
                  (N.StartDate <= @aToDate)
              ))
          ))
      ) AS RF ON (RF.ClientBranchProductID = BP.ClientBranchProductID)
  WHERE
    (BR.BranchID IS NOT NULL) AND (BR.BranchID > 0) AND
    (PR.ProductID IS NOT NULL) AND (PR.ProductID > 0) AND
    (BP.ConsultantID IS NOT NULL) AND (BP.ConsultantID > 0) AND
    (CS.ClientID IS NOT NULL) AND (CS.ClientID > 0) AND
    ((CS.ExcludeFromReport IS NULL) OR (CS.ExcludeFromReport = 0))

    @pBranchID
    @pProductID
    @pConsultantID

) AS Y WHERE (Y.Retainer > 0)
GROUP BY 1,2,3
ORDER BY 3