SELECT * FROM (

  SELECT
    Y.BranchID,
    Y.BranchRegion,
    Y.ProductID,
    Y.ProductCategory,
    Y.ConsultantID,
    Y.Consultant,
    Y.ConsultantEmployeeCode,
    Y.SalesCloserID,
    Y.SalesCloser,
    Y.SalesCloserEmployeeCode,
    Y.TechnicalSpecialistID,
    Y.TechnicalSpecialist,
    Y.TechnicalSpecialistEmployeeCode,
    Y.SalesReferrerID,
    Y.SalesReferrer,
    Y.SalesReferrerEmployeeCode,
    Y.ClientID,
    Y.Client,
    Y.FinNumber,
    Y.EntryType,
    Y.ClientBranchName,
    Y.ProductName,
    SUM(Y.Difference) AS Difference,
    MAX(Y.HeadOffice) AS HeadOffice,
    MIN(Y.StartDate) AS StartDate,
    SUM(Y.SalesData) AS SalesData,
    SUM(Y.CancelData) AS CancelData,
    SUM(Y.ReactivationData) AS ReactivationData,
    SUM(Y.DecreaseData) AS DecreaseData,
    SUM(Y.IncreaseData) AS IncreaseData,
    SUM(Y.AutoData) AS AutoData,
    SUM(Y.SalesData + Y.CancelData + Y.ReactivationData + Y.IncreaseData + Y.DecreaseData + Y.AutoData) AS GrowthData,
    SUM(Y.ConvertOldData) AS ConvertOldData,
    SUM(Y.ConvertNewData) AS ConvertNewData,
    SUM(Y.ReallocateData) AS ReallocateData,
    SUM(Y.Difference) AS Total,
    SUM(Y.CloseingRetainer) AS CloseingRetainer
  FROM (

    SELECT
      BR.BranchID,
      BR.BranchRegion,
      IF(P3.TrackingCategory IS NULL, CP.ProductID, P1.ProductID) AS ProductID,
      IF(P3.TrackingCategory IS NULL, PR.ProductCategory, P2.ProductCategory) AS ProductCategory,
      BP.ConsultantID,
      COALESCE(U1.GivenName, 'Unknown') AS Consultant,
      COALESCE(U1.EmployeeCode, 'Unknown') AS ConsultantEmployeeCode,
      BP.SalesCloserID,
      COALESCE(U2.GivenName, 'Unknown') AS SalesCloser,
      COALESCE(U2.EmployeeCode, 'Unknown') AS SalesCloserEmployeeCode,
      BP.TechnicalSpecialistID,
      COALESCE(U3.GivenName, 'Unknown') AS TechnicalSpecialist,
      COALESCE(U3.EmployeeCode, 'Unknown') AS TechnicalSpecialistEmployeeCode,
      BP.SalesReferrerID,
      COALESCE(U4.GivenName, 'Unknown') AS SalesReferrer,
      COALESCE(U4.EmployeeCode, 'Unknown') AS SalesReferrerEmployeeCode,
      IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.ClientID, CS.ParentClientID), CS.ClientID) AS ClientID,
      IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.FinNumber, CSP.FinNumber), CS.FinNumber) AS FinNumber,
      IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.GroupName, CSP.GroupName), CS.GroupName) AS Client,
      PF.EntryType,
      PF.Difference,
      CB.BranchName AS ClientBranchName,
      IF(CB.IsHeadOffice > 0, '<b>Head Office</b>', 'branch') AS HeadOffice,
      IF(P3.TrackingCategory IS NULL, PR.ProductName, P2.ProductName) AS ProductName,
      PF.StartDate,
      IF ((PF.EntryType = 'N'),                            PF.Difference, 0) AS SalesData,
      IF ((PF.EntryType = 'C') OR ((PF.EntryType = 'S') AND (PF.Difference < 0)), PF.Difference, 0) AS CancelData,
      IF (((PF.EntryType = 'S') AND (PF.Difference >= 0)), PF.Difference, 0) AS ReactivationData,
      IF ((PF.EntryType = 'D'),                            PF.Difference, 0) AS ConvertOldData,
      IF ((PF.EntryType = 'P'),                            PF.Difference, 0) AS ConvertNewData,
      IF (((PF.EntryType = 'M') AND (PF.Difference < 0)),  PF.Difference, 0) AS DecreaseData,
      IF (((PF.EntryType = 'M') AND (PF.Difference >= 0)), PF.Difference, 0) AS IncreaseData,
      IF ((PF.EntryType = 'A'),                            PF.Difference, 0) AS AutoData,
      IF ((PF.EntryType = 'R'),                            PF.Difference, 0) AS ReallocateData,
      PF.Difference AS Total,
      PF.RetainerFee AS CloseingRetainer
    FROM
      taClientProductFees AS PF
        LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = PF.ClientBranchProductID)
        LEFT JOIN taClientBranches       AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
        LEFT JOIN taClientProducts       AS CP ON (CP.ClientProductID = BP.ClientProductID)
        LEFT JOIN taClients              AS CS ON (CS.ClientID = CB.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 taUsers                AS U3 ON (U3.UserID = BP.TechnicalSpecialistID)
        LEFT JOIN taUsers                AS U4 ON (U4.UserID = BP.SalesReferrerID)
        LEFT JOIN taBranches             AS BR ON (BR.BranchID = CB.LabourNetBranchID)
        LEFT JOIN taClientProducts       AS P1 ON (P1.ClientProductID = CP.ParentClientProductID)
        LEFT JOIN taProducts             AS P2 ON (P2.ProductID       = P1.ProductID)
        LEFT JOIN taProductCategory      AS P3 ON (P3.ProductCategory = P2.ProductCategory)
        LEFT JOIN taClients              AS CSP ON (CSP.ClientID = CS.ParentClientID)
    WHERE
      (ABS(PF.Difference) > 0.001) AND
      (BR.BranchID IS NOT NULL) AND
      (PR.ProductID IS NOT NULL) AND
      ((CS.ExcludeFromReport IS NULL) OR (CS.ExcludeFromReport = 0)) AND
      (PF.StartDate BETWEEN @aFromDate AND @aToDate)

      @pBranchID
      @pProductID
      @pConsultantID
      @pClientID

      @pBranchRegion
      @pProductCategory
      @pConsultant
      @pClient

  ) AS Y
  GROUP BY
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22

) AS X
