SELECT

  --
  -- Consultant Fields.
  --
  U1.FullName AS ClientConsultant,
  GROUP_CONCAT(DISTINCT BR.BranchRegion ORDER BY BR.BranchRegion SEPARATOR ' + ') AS Regions,

  PR.ProductName,

  --
  -- taClient Fields.
  --
  CS.ClientID AS ClientID,
  CS.FinNumber AS FinNumber,
  CS.GroupName AS GroupName,
  IF((CS.ClientStatus = 1), 'Active', 'Cancelled') AS ClientStatus,
  CS.EstTurnOver AS EstTurnOver,
  BP.JoinDate AS DateJoined,
  SM.ClientGroup,
  IF((CS.EmployersOrganisation IS NULL) OR (TRIM(CS.EmployersOrganisation) = ''), "No", CS.EmployersOrganisation) AS EmployersOrganisation,
  IF(CB.IsHeadOffice > 0, '<b>HO</b>', 'branch') AS HeadOffice,
  IF(fnClientLabourNetRegionCount(CS.ClientID) > 1, 'National', 'Local') AS National,
  CONCAT(CS2.GroupName, ' [', CS2.FinNumber, ']') AS ReferredBy,
  SUM(CB.NoOfEmployees) AS EmployeeCount,

  --
  -- Retainer.
  --
  ROUND(SUM(fnClientBranchProductRetainer(BP.ClientBranchProductID, IF((BP.Status = 1), NOW(), BP.TerminationDate))) * 1.0, 2) AS Retainer
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 taProducts       AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
    LEFT JOIN taUsers          AS U1 ON (U1.UserID = BP.ConsultantID)
    LEFT JOIN taClients        AS CS ON (CS.ClientID = CB.ClientID)
    LEFT JOIN taClientSummary  AS SM ON (SM.ClientID = CB.ClientID)
    LEFT JOIN taClients        AS CS2 ON (CS2.ClientID = CS.ReferredByClientID)
WHERE
  ((TRIM(@aDateJoinedFrom) = '') OR (CP.JoinDate >= @aDateJoinedFrom))
  @pProductID
  @pBranchID
  @pClientStatus
GROUP BY
  BP.ConsultantID,
  CS.ClientID
ORDER BY
  ClientID
