SELECT
  CONCAT(IF(Y.ClientID IS NULL, '1', '2'), '_', IF(SM.National = 'National', '1', '2')) AS ReportGroup,
  IF(Y.ClientID IS NULL, 'NewClient', 'CrossSiloClient') AS NewClientGroup,
  CONCAT(LEFT(@nYearMonth, 4), '-', RIGHT(@nYearMonth, 2)) AS YearMonth,
  CS.ClientID,
  CS.FinNumber,
  CS.GroupName,
  CS.EstTurnOver,
  SM.ClientGroup,
  CS.DateJoined,
  SM.National,
  SM.LabourNetBranch,
  IF(Y.ClientID IS NULL, COUNT(DISTINCT(CS.ClientID)), 0) AS ClientCount,
  COUNT(DISTINCT(IF(PR.ProductCategory = 'SE CONSULTING', PR.ProductName, PR.ProductCategory))) AS ProductCount,
  GROUP_CONCAT(DISTINCT IF(PR.ProductCategory = 'SE CONSULTING', PR.ProductName, PR.ProductCategory) ORDER BY IF(PR.ProductCategory = 'SE CONSULTING', PR.ProductName, PR.ProductCategory) SEPARATOR ' + ') AS ProductCategory,
  COUNT(DISTINCT(BR.BranchName)) AS RegionCount,
  GROUP_CONCAT(DISTINCT BR.BranchName ORDER BY BR.BranchName SEPARATOR ' + ') AS Regions,
  SUM(COALESCE(X.Retainer, 0.0)) AS Retainer
FROM
  taClientRetentionSummary AS RS
    LEFT JOIN taClients       AS CS ON (CS.ClientID = RS.ClientID)
    LEFT JOIN taClientSummary AS SM ON (SM.ClientID = RS.ClientID)
    LEFT JOIN (
      SELECT DISTINCT ClientID FROM taClientRetentionSummary WHERE (YearMonth < @nYearMonth)
    ) AS Y ON (Y.ClientID = RS.ClientID)
    INNER JOIN (
      SELECT DISTINCT
        A.ClientID,
        A.LabourNetBranchID,
        A.ProductID,
        A.Retainer
      FROM
        taClientRetentionSummary AS A
          LEFT JOIN (
            SELECT DISTINCT
              ClientID,
              LabourNetBranchID,
              ProductID
            FROM
              taClientRetentionSummary
            WHERE
              (YearMonth < @nYearMonth)
          ) AS B USING (ClientID, LabourNetBranchID, ProductID)
      WHERE
        (A.YearMonth = @nYearMonth) AND
        (B.ClientID IS NULL)
    ) AS X ON ((X.ClientID = RS.ClientID) AND (X.LabourNetBranchID = RS.LabourNetBranchID) AND (X.ProductID = RS.ProductID))
    LEFT JOIN taProducts AS PR ON (PR.ProductID = X.ProductID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = X.LabourNetBranchID)
WHERE
  ((CS.ExcludeFromReport IS NULL) OR (CS.ExcludeFromReport = 0)) AND
  (RS.YearMonth = @nYearMonth) AND
  IF(Y.ClientID IS NULL, (DATE_FORMAT(CS.DateJoined, "%Y%m") = @nYearMonth), TRUE)
  @pProductID
  @pBranchID
  @pSalesCloserID
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11
