SELECT
  Y.ClientID,
  Y.ClientName,
  Y.ClientBranchProductID,
  Y.ClientBranchName,
  Y.LabourNetBranchID,
  Y.LabourNetBranchName,
  Y.LabourNetBranchRegion,
  Y.IsHeadOffice,
  Y.ProductCategory,
  Y.AllocatedConsultantID,
  Y.AllocatedConsultant,
  Y.Batch,
  SUM(Y.RetainerFee1) AS CurrentRetainerFee,
  SUM(Y.ServiceValue12) AS ServiceValue12,
  SUM(Y.ServiceValue11) AS ServiceValue11,
  SUM(Y.ServiceValue10) AS ServiceValue10,
  SUM(Y.ServiceValue9) AS ServiceValue9,
  SUM(Y.ServiceValue8) AS ServiceValue8,
  SUM(Y.ServiceValue7) AS ServiceValue7,
  SUM(Y.ServiceValue6) AS ServiceValue6,
  SUM(Y.ServiceValue5) AS ServiceValue5,
  SUM(Y.ServiceValue4) AS ServiceValue4,
  SUM(Y.ServiceValue3) AS ServiceValue3,
  SUM(Y.ServiceValue2) AS ServiceValue2,
  SUM(Y.ServiceValue1) AS ServiceValue1,
  SUM(Y.RetainerFee12 + Y.RetainerFee11 + Y.RetainerFee10 + Y.RetainerFee9 + Y.RetainerFee8 + Y.RetainerFee7 +
      Y.RetainerFee6  + Y.RetainerFee5  + Y.RetainerFee4  + Y.RetainerFee3 + Y.RetainerFee2 + Y.RetainerFee1) AS AnnualRetainerFee,
  SUM(Y.ServiceValue12 + Y.ServiceValue11 + Y.ServiceValue10 + Y.ServiceValue9 + Y.ServiceValue8 + Y.ServiceValue7 +
      Y.ServiceValue6  + Y.ServiceValue5  + Y.ServiceValue4  + Y.ServiceValue3 + Y.ServiceValue2 + Y.ServiceValue1) AS TotalServiceValue
FROM (

  SELECT
    CS.ClientID,
    CS.GroupName AS ClientName,
    PH.ClientBranchProductID,
    CB.BranchName AS ClientBranchName,
    CB.LabourNetBranchID,
    CB.IsHeadOffice,
    BR.BranchName AS LabourNetBranchName,
    BR.BranchRegion AS LabourNetBranchRegion,
    PR.ProductCategory,
    BP.ConsultantID AS AllocatedConsultantID,
    U1.FullName AS AllocatedConsultant,
    CP.Batch,

    SUM(IF(PH.BillingYearMonth = @nToDate12, PH.RetainerFee, 0.0)) AS RetainerFee12,
    SUM(IF(PH.BillingYearMonth = @nToDate11, PH.RetainerFee, 0.0)) AS RetainerFee11,
    SUM(IF(PH.BillingYearMonth = @nToDate10, PH.RetainerFee, 0.0)) AS RetainerFee10,
    SUM(IF(PH.BillingYearMonth = @nToDate09, PH.RetainerFee, 0.0)) AS RetainerFee9,
    SUM(IF(PH.BillingYearMonth = @nToDate08, PH.RetainerFee, 0.0)) AS RetainerFee8,
    SUM(IF(PH.BillingYearMonth = @nToDate07, PH.RetainerFee, 0.0)) AS RetainerFee7,
    SUM(IF(PH.BillingYearMonth = @nToDate06, PH.RetainerFee, 0.0)) AS RetainerFee6,
    SUM(IF(PH.BillingYearMonth = @nToDate05, PH.RetainerFee, 0.0)) AS RetainerFee5,
    SUM(IF(PH.BillingYearMonth = @nToDate04, PH.RetainerFee, 0.0)) AS RetainerFee4,
    SUM(IF(PH.BillingYearMonth = @nToDate03, PH.RetainerFee, 0.0)) AS RetainerFee3,
    SUM(IF(PH.BillingYearMonth = @nToDate02, PH.RetainerFee, 0.0)) AS RetainerFee2,
    SUM(IF(PH.BillingYearMonth = @nToDate01, PH.RetainerFee, 0.0)) AS RetainerFee1,

    SUM(IF(PH.BillingYearMonth = @nToDate12, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue12,
    SUM(IF(PH.BillingYearMonth = @nToDate11, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue11,
    SUM(IF(PH.BillingYearMonth = @nToDate10, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue10,
    SUM(IF(PH.BillingYearMonth = @nToDate09, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue9,
    SUM(IF(PH.BillingYearMonth = @nToDate08, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue8,
    SUM(IF(PH.BillingYearMonth = @nToDate07, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue7,
    SUM(IF(PH.BillingYearMonth = @nToDate06, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue6,
    SUM(IF(PH.BillingYearMonth = @nToDate05, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue5,
    SUM(IF(PH.BillingYearMonth = @nToDate04, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue4,
    SUM(IF(PH.BillingYearMonth = @nToDate03, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue3,
    SUM(IF(PH.BillingYearMonth = @nToDate02, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue2,
    SUM(IF(PH.BillingYearMonth = @nToDate01, PH.CoverUsed + PH.FreeTimeUsed, 0.0)) AS ServiceValue1
  FROM
    taClientBranchProductHistory       AS PH
      LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = PH.ClientBranchProductID)
      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        = CB.ClientID)
      LEFT JOIN taProducts             AS PR ON (PR.ProductID       = CP.ProductID)
      LEFT JOIN taUsers                AS U1 ON (U1.UserID          = BP.ConsultantID)
      LEFT JOIN taBranches             AS BR ON (BR.BranchID        = CB.LabourNetBranchID)
  WHERE
    (PH.BillingYearMonth >= @nToDate12) AND
    (PH.BillingYearMonth <= @nToDate01) AND
    ((@nClientStatus < 0) OR (@nClientStatus = CS.ClientStatus))

    @pProductID
    @pBranchID
    @pClientID

  GROUP BY
    1,2,3,4,5,6,7,8,9,10,11

) AS Y
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11
