SELECT
  CONCAT(IF(Y.ClientID IS NULL, '1', '2'), '_', IF(SM.National = 'National', '1', '2')) AS ReportGroup,
  IF(Y.ClientID IS NULL, 'LostClient', 'CrossSiloClient') AS NewClientGroup,
  CONCAT(LEFT(@nYearMonth, 4), '-', RIGHT(@nYearMonth, 2)) AS YearMonth,
  CS.ClientID,
  CS.FinNumber,
  CS.GroupName,
  CS.EstTurnOver,
  SM.ClientGroup,
  CS.DateJoined,
  CS.TerminationDate,
  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(fnClientBranchProductRetainer(BP.ClientBranchProductID, DATE_ADD(BP.TerminationDate, INTERVAL -1 DAY)), 0)) AS Retainer
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 taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
    LEFT JOIN (
      SELECT DISTINCT ClientID FROM taClients WHERE (TerminationDate IS NULL) OR (TerminationDate = '0000-00-00')
    ) AS Y ON (Y.ClientID = CS.ClientID)
WHERE
  (BP.ConsultantID IS NOT NULL) AND
  (BP.TerminationDate IS NOT NULL) AND
  (BP.TerminationDate <> '0000-00-00') AND
  (DATE_FORMAT(BP.TerminationDate, "%Y%m") = @nYearMonth) AND

  ((CS.ParentClientID IS NULL) OR (CS.ParentClientID = 0)) AND
  (CS.ClientID NOT IN (

    SELECT ClientID FROM (
      SELECT
        CS.ClientID,
        CAST(JSON_VALUE(IF(COALESCE(SO.OptionValue, '') = '', '{"Internal":0}', SO.OptionValue), '$.Internal') AS INT) AS Internal
      FROM
        taClients AS CS
          LEFT JOIN taSystemOption AS SO ON ((SO.OptionType = 'TERMINATION') AND (SO.OptionDescription = CS.TerminationReason))
      WHERE
        (CS.ClientStatus = 0) AND
        (CS.TerminationReason IS NOT NULL)
    ) AS X WHERE (Internal > 0)

  ))

  @pBranchID
  @pProductID
  @pSalesCloserID
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11,12
