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,
  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)
WHERE
  (BP.ConsultantID IS NOT NULL) AND
  (BP.TerminationDate IS NOT NULL) AND
  (BP.TerminationDate <> '0000-00-00')
  @pFromTo

  @pBranchID
  @pProductID
  @pConsultantID

ORDER BY
  CS.GroupName,
  BP.TerminationDate