SELECT * FROM (

  SELECT
    CP.ClientID,
    CS.GroupName,
    CS.PeasaMember,
    PR.ProductCategory,
    BP.ConsultantID,
    ROUND(SUM(COALESCE(fnClientBranchProductRetainer(BP.ClientBranchProductID, @aToDate), 0.0)), 2) AS Retainer
  FROM
    taClientBranchProducts AS BP
      LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
      LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
      LEFT JOIN taClients AS CS ON (CS.ClientID = CP.ClientID)
      LEFT JOIN (

        SELECT DISTINCT
          CS.ClientID,
          PR.ProductCategory,
          BP.ConsultantID
        FROM
          taClients AS CS
            INNER JOIN taClientBranches AS CB ON ((CB.ClientID = CS.ClientID) AND (CB.IsHeadOffice = 1) AND (CB.BranchStatus = 1))
            INNER JOIN taClientBranchProducts AS BP ON (BP.ClientBranchID = CB.ClientBranchID)
            INNER JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
            INNER JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)

      ) AS HO ON ((HO.ClientID = CP.ClientID) AND (HO.ProductCategory = PR.ProductCategory))
  WHERE
    (CS.ClientStatus = 1) AND
    (CP.Status = 1) AND
    (BP.Status = 1) AND
    (BP.ConsultantID > 0) AND
    (PR.ProductCategory IN ('HEALTH AND SAFETY', 'HR', 'IR', 'PAYROLL', 'TRAINING')) AND
    (CP.ProductID <> 14) AND
    (BP.JoinDate <= @aToDate) AND
    (BP.ConsultantID = HO.ConsultantID) AND
    ((BP.TerminationDate IS NULL) OR (BP.TerminationDate > @aToDate)) AND
    (BP.ClientBranchID IN (SELECT ConsultantClientBranchID FROM ttCrossSiloConsultantBranches WHERE (ConsultantID = @nConsultantID)))

    @pClientID

  GROUP BY 1,2,3,4,5

) AS X
WHERE
  (X.Retainer > 0.0)
ORDER BY
  X.GroupName