SELECT
  X.GroupName,
  X.AllocatedConsultant,
  X.SalesPerson,
  X.ClientID,
  X.FinNumber,
  X.Industry,
  X.ClientBranch,
  X.NoOfEmployees,
  X.BranchRegion,
  X.ProductName,
  SUM(X.Retainer) AS Retainer,
  MIN(X.JoinDate) AS JoinDate,
  MIN(X.FirstRetainerInvoiceDate) AS FirstRetainerInvoiceDate,
  MAX(X.RetainerInvoiceCount) AS RetainerInvoiceCount,
  MAX(X.PaidRetainerInvoiceCount) AS PaidRetainerInvoiceCount,
  MAX(X.InvoicesDueCount) AS InvoicesDueCount,
  MAX(X.MonthsActive) AS MonthsActive,
  MIN(X.TerminationDate) AS TerminationDate,
  GROUP_CONCAT(DISTINCT X.TerminationReason) AS TerminationReason,
  GROUP_CONCAT(DISTINCT X.TerminationNotes) AS TerminationNotes
FROM (

  SELECT
    CS.GroupName,
    U1.FullName AS AllocatedConsultant,
    U2.FullName AS SalesPerson,
    CP.ClientID,
    CS.FinNumber,
    S1.OptionDescription AS Industry,
    CB.BranchName AS ClientBranch,
    CB.NoOfEmployees,
    BR.BranchRegion,
    -- CONCAT('(', PR.ProductCategory, ') ', PR.ProductName) AS ProductName,
    IF(P3.TrackingCategory IS NULL, CONCAT('(', PR.ProductCategory, ') ', PR.ProductName), CONCAT('(', P2.ProductCategory, ') ', P2.ProductName)) AS ProductName,
    fnClientBranchProductRetainer(BP.ClientBranchProductID, DATE_ADD(BP.TerminationDate, INTERVAL -1 DAY)) * 1.0 AS Retainer,
    BP.JoinDate,
    X.FirstRetainerInvoiceDate,
    X.RetainerInvoiceCount,
    IF(X.RetainerInvoiceCount < SM.InvoicesDueCount, 0, (X.RetainerInvoiceCount - SM.InvoicesDueCount)) AS PaidRetainerInvoiceCount,
    SM.InvoicesDueCount,
    ROUND(TIMESTAMPDIFF(MONTH, X.FirstRetainerInvoiceDate, BP.TerminationDate) +
      DATEDIFF(BP.TerminationDate, X.FirstRetainerInvoiceDate + INTERVAL TIMESTAMPDIFF(MONTH, X.FirstRetainerInvoiceDate, BP.TerminationDate) MONTH) /
      DATEDIFF(X.FirstRetainerInvoiceDate + INTERVAL TIMESTAMPDIFF(MONTH, X.FirstRetainerInvoiceDate, BP.TerminationDate) + 1 MONTH, X.FirstRetainerInvoiceDate + INTERVAL TIMESTAMPDIFF(MONTH, X.FirstRetainerInvoiceDate, BP.TerminationDate) MONTH), 1) AS MonthsActive,
    BP.TerminationDate,
    IF(BP.TerminationReason = '0', '', BP.TerminationReason) AS TerminationReason,
    BP.TerminationNotes
  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 taClientSummary  AS SM ON (SM.ClientID = CP.ClientID)
      LEFT JOIN taProducts       AS PR ON (PR.ProductID = CP.ProductID)
      LEFT JOIN taUsers          AS U1 ON (U1.UserID = BP.ConsultantID)
      LEFT JOIN taUsers          AS U2 ON (U2.UserID = BP.SalesCloserID)
      LEFT JOIN taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
      LEFT JOIN taSystemOption   AS S1 ON ((S1.OptionType = 'INDUSTRY') AND (S1.OptionID = CS.IndustryID))
      LEFT JOIN (
        SELECT
          IH.ClientID,
          MIN(IH.DocumentDate) AS FirstRetainerInvoiceDate,
          COUNT(*) AS RetainerInvoiceCount
        FROM
          taInvoiceHeader AS IH
        WHERE
          (IH.InvoiceType = 'R') AND
          (IH.DocumentNumber <> 'VOID')
        GROUP BY
          IH.ClientID
      ) AS X ON (X.ClientID = CS.ClientID)
      LEFT JOIN taClientProducts  AS P1 ON (P1.ClientProductID = CP.ParentClientProductID)
      LEFT JOIN taProducts        AS P2 ON (P2.ProductID       = P1.ProductID)
      LEFT JOIN taProductCategory AS P3 ON (P3.ProductCategory = P2.ProductCategory)
  WHERE
    (BP.ConsultantID IS NOT NULL) AND
    (BP.TerminationDate IS NOT NULL) AND
    (BP.TerminationDate <> '0000-00-00')
    @pFromTo

    @pBranchID
    @pProductID
    @pConsultantID

) AS X
GROUP BY
  1,2,3,4,5,6,7,8,9,10
ORDER BY
  X.GroupName,
  X.TerminationDate