SELECT DISTINCT
  CONCAT(IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, RS.FinNumber, CSP.FinNumber), RS.FinNumber), '_', PR.ProductCategory) AS ElementID
FROM
  taClientRetentionSummary AS RS
    LEFT JOIN taClients AS CS ON (CS.ClientID = RS.ClientID)
    LEFT JOIN taClients AS CSP ON (CSP.ClientID = CS.ParentClientID)
    LEFT JOIN (
      SELECT
        CS1.FinNumber,
        CB1.LabourNetBranchID
      FROM
        taClients AS CS1
          LEFT JOIN taClientBranches CB1 ON (CB1.ClientID = CS1.ClientID)
      WHERE
        (CB1.IsHeadOffice = 1) AND
        ((CB1.BranchStatus = 1) OR (CS1.ClientStatus = 0))
    ) AS HO ON (HO.FinNumber = RS.FinNumber)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = RS.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
WHERE
  (RS.YearMonth = @nYearMonth) AND
  ((PC.MustHaveParent = 0) OR (PC.MustHaveParent IS NULL))

  @pBranchID
  @pProductID

ORDER BY 1
